Copy Dynamic Range from Master Workbook to Archive Workbook

NullaDave · Jun 12th 2014

Hi All,

I have been using this resource for a while and have always found it incredibly useful. The problem I have is very easily solved I'm sure, but I just can't seem to work it out. I have a master workbook that holds rows of data sent from a Userform. Once the data has been analysed it needs to be Archived periodically. The Achive file is in another location. Now I have managed to adapt the below code to copy the rows to another sheet in the same workbook but can't seem to adapt the code to move it to the other workbook. I am sure it's just a case of changing the destination file but so far it has me stumped.

If anyone can offer an alternative code or an amendment to mine then it will be much appreciated.

Code

Sub cmdArchive()
     
    Dim lngLastRow As Long, lngPasteRow As Long
    
    Application.ScreenUpdating = False
                  
        lngLastRow = Sheets("tblRecord").Range("A:G").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
         
        If WorksheetFunction.CountA(Sheets("tblTest").Cells) = 0 Then
        lngPasteRow = 1
            
    Else
                
        lngPasteRow = Sheets("tblTest").Range("A:G").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
          
    End If
         
        Sheets("tblRecord").Range("A1:G" & lngLastRow).Copy _
        Destination:=Sheets("tblTest").Range("A" & lngPasteRow)
         
        Application.ScreenUpdating = True
    
    With Sheets("tblRecord")
        .Range("A2", .Cells(.Rows.Count, .Columns.Count)).Clear
        
    End With
     
End Sub

Display More

This code copies the rows to the "tblTest" worksheet but how do I get it to copy to the "Archive Workbook" instead. For the purpose of an example, the "Archive Workbook" is saved in c:\Excel\Archive

Thanks

**Luke M** · Jun 12th 2014

Re: Copy Dynamic Range from Master Workbook to Archive Workbook

This should help you out.

Code

Sub ArchiveRows()
Dim lngLastRow As Long, lngPasteRow As Long
Dim DestWB As Workbook
Dim DestPath As String




'Where is the file?
DestPath = "c:\Excel\Archive\Archive Workbook.xlsx"




    Application.ScreenUpdating = False
    'IMPORTANT: Keep track of parent WB
    lngLastRow = ThisWorkbook.Sheets("tblRecord").Range("A:G").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    'Open Archive
    Set DestWB = Workbooks.Open(DestPath)
    
    If WorksheetFunction.CountA(DestWB.Sheets("tblTest").Cells) = 0 Then
        lngPasteRow = 1
    Else
        lngPasteRow = DestWB.Sheets("tblTest").Range("A:G").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    End If
    
     
    ThisWorkbook.Sheets("tblRecord").Range("A1:G" & lngLastRow).Copy _
    Destination:=DestWB.Sheets("tblTest").Range("A" & lngPasteRow)
    
    'Save and close
    DestWB.Close SaveChanges:=True
     
    Application.ScreenUpdating = True
     
    With Sheets("tblRecord")
        .Range("A2", .Cells(.Rows.Count, .Columns.Count)).Clear
         
    End With
     
End Sub

Display More

NullaDave · Jun 13th 2014

Re: Copy Dynamic Range from Master Workbook to Archive Workbook

Hi Luke M

This works perfectly! I had the same idea of opening both workbooks but I was getting tied up keeping track of them once they were open.

Thanks for your help

Copy Dynamic Range from Master Workbook to Archive Workbook

Participate now!

Share