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.
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
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