none
Updating data from one sheet to another using one point of reference VBA Excel. RRS feed

  • Question

  • I have created a script that will for all intended purposes will copy the cell values in the form on sheet Print-Edit NCMR, to NCMR Data. But before doing so it will reference a cell on the far right of Print-Edit NCMR and verify the row exists (which it has to because that is where the data originally comes from) and then copy all data to a single row in Print-Edit NCMR, and paste it into the existing row of NCMR Data which then will be overwritten with the new info, but not have it's main identifier replaced. 

    Option Explicit
    
    Sub PENCMR()
        Dim i As Integer
    
        With Application
            .ScreenUpdating = False
        End With
    
        'Internal NCMR
        Dim wsPE As Worksheet
        Dim wsNDA As Worksheet
    
        'Copy Ranges
        Dim c As Variant
    
        'Paste Ranges
        Dim p As Range
    
        'Setting Sheet
        Set wsPE = Sheets("Print-Edit NCMR")
        Set wsNDA = Sheets("NCMR Data")
        Set p = wsPE.Range("A54")
    
        With wsPE
            c = Array(.Range("AG2"), .Range("B11"), .Range("B14"), .Range("B17"), .Range("B20"), .Range("B23") _
                    , .Range("Q11"), .Range("Q14"), .Range("Q17"), .Range("Q20"), .Range("R25"), .Range("V23") _
                    , .Range("V25"), .Range("V27"), .Range("B32"), .Range("B36"), .Range("B40"), .Range("B44") _
                    , .Range("D49"), .Range("L49"), .Range("V49"))
        End With
    
        For i = LBound(c) To UBound(c)
            p(i + 1).Value = c(i).Value
        Next
    
        With wsNDA
            Dim rFind As Long, NR As Long, LR As Long, LC As Long
            LR = .Range("C" & Rows.Count).End(xlUp).Row
            LC = .Cells(2, Columns.Count).End(xlToLeft).Column
            NR = LR + 1
            rFind = wsNDA.Range("A25:A" & LR).Find(Range("A54")).Row
    
            .Range("A54", .Cells(2, LC)).Copy
            .Range("A" & rFind).PasteSpecial xlPasteValues
            .Range("A54", .Cells(1, LC)).ClearContents
        End With
    
        With Application
            .ScreenUpdating = True
        End With
    End Sub
    

    I thought I got this working, but it replaces everything with blank spaces, not the data required. If someone could help me figure out why it would be greatly appreciated. I've gotten this far, but I think I've hit my limit on understanding. 


    Tuesday, May 1, 2012 7:45 PM

Answers

  • One problemmay be from this line:

            rFind = wsNDA.Range("A25:A" & LR).Find(Range("A54").Value).Row

    In it, the Range("A54") refers to A54 of the activesheet, which may or may not be wsNDA. You could use this, since you have the With wsNDA

            rFind = .Range("A25:A" & LR).Find(.Range("A54").Value).Row

    Other than that, it is hard to debug your code without the actual workbook, since workbook layout is very important to your code.


    HTH, Bernie

    • Marked as answer by Matt Ridge Friday, June 1, 2012 12:48 PM
    Tuesday, May 1, 2012 11:12 PM

All replies

  • One problemmay be from this line:

            rFind = wsNDA.Range("A25:A" & LR).Find(Range("A54").Value).Row

    In it, the Range("A54") refers to A54 of the activesheet, which may or may not be wsNDA. You could use this, since you have the With wsNDA

            rFind = .Range("A25:A" & LR).Find(.Range("A54").Value).Row

    Other than that, it is hard to debug your code without the actual workbook, since workbook layout is very important to your code.


    HTH, Bernie

    • Marked as answer by Matt Ridge Friday, June 1, 2012 12:48 PM
    Tuesday, May 1, 2012 11:12 PM
  • One problemmay be from this line:

            rFind = wsNDA.Range("A25:A" & LR).Find(Range("A54").Value).Row

    In it, the Range("A54") refers to A54 of the activesheet, which may or may not be wsNDA. You could use this, since you have the With wsNDA

            rFind = .Range("A25:A" & LR).Find(.Range("A54").Value).Row

    Other than that, it is hard to debug your code without the actual workbook, since workbook layout is very important to your code.


    HTH, Bernie

    Here is the workbook, I don't have the ability to attach files for some reason, so bare with me. 

    Workbook Ziped Link

    I hope this helps. 


    Wednesday, May 2, 2012 3:17 PM