Hello, all.
What I am trying to do is run a macro to merge data from different workbooks and deposit it into a "master" workbook that already has previous data. The way I originally wrote it had the following steps:
Below is the code I fabricobbled up. The issue is sometimes it works flawlessly, but sometimes it doesn't copy all the rows of data from the source workbooks. It'll leave the last 1 to 8 or so rows and everything before that will have been cut/pasted out into the aggregator then into the master. The number of leftover rows and which data workbooks this happens in seems to be totally random.
I am sure this could be done in a much much easier way with many fewer lines. What's driving me nuts is why it's not working. It seems so straightforward.
Am I overlooking something or should I just dump this method and try something else?
Any help would be greatly appreciated.
Cheers!
What I am trying to do is run a macro to merge data from different workbooks and deposit it into a "master" workbook that already has previous data. The way I originally wrote it had the following steps:
- Open the (lets call it the aggregator) workbook that will handle the merging. It has 2 tabs, one with the button to run the VBA and one to act as the middle man for the data.
- Click the ActiveX button which opens the first workbook with data, copies all the rows of data into the aggregator's second tab, deletes the source data, then saves and closes the first workbook with data (now empty).
- Repeat the previous step for the next 5 data source workbooks.
- Open the "master" workbook, find the first empty row and cut/paste the data from the aggregator's 2nd tab into the master.
- Save and close the master.
Below is the code I fabricobbled up. The issue is sometimes it works flawlessly, but sometimes it doesn't copy all the rows of data from the source workbooks. It'll leave the last 1 to 8 or so rows and everything before that will have been cut/pasted out into the aggregator then into the master. The number of leftover rows and which data workbooks this happens in seems to be totally random.
Code:
Private Sub CommandButton1_Click()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Workbooks.Open ("[URL="file://\\server01\PSI-Share\Inspection"]\\[/URL]Applied Labor Data\ShopPC4.xlsx")
Worksheets("Work Data").Activate
Worksheets("Work Data").Select
lastCol = 28
LastRow = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row
ActiveSheet.Range("a1", ActiveSheet.Cells(LastRow, lastCol)).Select
Selection.Cut
Workbooks("Auto Merger.xlsm").Worksheets("Data Sheet").Activate
Workbooks("Auto Merger.xlsm").Worksheets("Data Sheet").Select
ActiveSheet.Range("a1").Select
ActiveSheet.Paste
Workbooks("ShopPC4.xlsx").Close SaveChanges:=True
Application.Workbooks.Open ("[URL="file://\\server01\PSI-Share\Inspection"]\\[/URL]Applied Labor Data\ShopPC2.xlsx")
Worksheets("Work Data").Activate
Worksheets("Work Data").Select
lastRow2 = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row
ActiveSheet.Range("a1", ActiveSheet.Cells(lastRow2, lastCol)).Select
Selection.Cut
Workbooks("Auto Merger.xlsm").Worksheets("Data Sheet").Activate
Workbooks("Auto Merger.xlsm").Worksheets("Data Sheet").Select
ActiveSheet.Range("a1").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Workbooks("ShopPC2.xlsx").Close SaveChanges:=True
Application.Workbooks.Open ("[URL="file://\\server01\PSI-Share\Inspection"]\\[/URL]Applied Labor Data\ShopPC3.xlsx")
Worksheets("Work Data").Activate
Worksheets("Work Data").Select
lastRow3 = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row
ActiveSheet.Range("a1", ActiveSheet.Cells(lastRow3, lastCol)).Select
Selection.Cut
Workbooks("Auto Merger.xlsm").Worksheets("Data Sheet").Activate
Workbooks("Auto Merger.xlsm").Worksheets("Data Sheet").Select
ActiveSheet.Range("a1").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Workbooks("ShopPC3.xlsx").Close SaveChanges:=True
Application.Workbooks.Open ("[URL="file://\\server01\PSI-Share\Inspection"]\\[/URL]Applied Labor Data\ShopPC1.xlsx")
Worksheets("Work Data").Activate
Worksheets("Work Data").Select
lastRow4 = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row
ActiveSheet.Range("a1", ActiveSheet.Cells(lastRow4, lastCol)).Select
Selection.Cut
Workbooks("Auto Merger.xlsm").Worksheets("Data Sheet").Activate
Workbooks("Auto Merger.xlsm").Worksheets("Data Sheet").Select
ActiveSheet.Range("a1").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Workbooks("ShopPC1.xlsx").Close SaveChanges:=True
Application.Workbooks.Open ("[URL="file://\\server01\PSI-Share\Inspection"]\\[/URL]Applied Labor Data\ShopPC5.xlsx")
Worksheets("Work Data").Activate
Worksheets("Work Data").Select
lastRow5 = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row
ActiveSheet.Range("a1", ActiveSheet.Cells(lastRow5, lastCol)).Select
Selection.Cut
Workbooks("Auto Merger.xlsm").Worksheets("Data Sheet").Activate
Workbooks("Auto Merger.xlsm").Worksheets("Data Sheet").Select
ActiveSheet.Range("a1").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Workbooks("ShopPC5.xlsx").Close SaveChanges:=True
Application.Workbooks.Open ("[URL="file://\\server01\PSI-Share\Inspection"]\\[/URL]Applied Labor Data\ShopPC6.xlsx")
Worksheets("Work Data").Activate
Worksheets("Work Data").Select
lastRow6 = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row
ActiveSheet.Range("a1", ActiveSheet.Cells(lastRow6, lastCol)).Select
Selection.Cut
Workbooks("Auto Merger.xlsm").Worksheets("Data Sheet").Activate
Workbooks("Auto Merger.xlsm").Worksheets("Data Sheet").Select
ActiveSheet.Range("a1").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Workbooks("ShopPC6.xlsx").Close SaveChanges:=True
Workbooks("Auto Merger.xlsm").Sheets("Data Sheet").Select
Workbooks("Auto Merger.xlsm").Sheets("Data Sheet").Activate
LastRow7 = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row
ActiveSheet.Range("a1", ActiveSheet.Cells(LastRow7, lastCol)).Select
Selection.Cut
Workbooks.Open ("[URL="file://\\server01\PSI-Share\Inspection"]\\[/URL]Applied Labor Data\Work Form Data.xlsx"), Password:="Password001"
Workbooks("Work Form Data.xlsx").Sheets("Work Data").Select
Workbooks("Work Form Data.xlsx").Sheets("Work Data").Activate
ActiveSheet.Range("a1").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Workbooks("Work Form Data.xlsx").Close SaveChanges:=True
Workbooks("Auto Merger.xlsm").Sheets("Button Sheet").Select
MsgBox ("All Done!")
End SubI am sure this could be done in a much much easier way with many fewer lines. What's driving me nuts is why it's not working. It seems so straightforward.
Am I overlooking something or should I just dump this method and try something else?
Any help would be greatly appreciated.
Cheers!
