Copy data from multiple sheets into a master sheet - VBA button stuck

[clairecatx]

Hello. I attempted to create a macro that copies data from multiple sheets and paste as values in a 'master' sheet, with the data from each sheet stacked without overlaps in the master sheet. Order is not important. All within the same workbook. When I run the code from Project Explorer, it works fine. But when I try to do so through a button, I get this error:

I've also experienced similar issues running the script multiple times in the same session through Project Explorer. However, the first time is usually fine.

Here's the code:

Sub Collate_Transactions_v4()

'4. Collate latest transactions (Mod 3)

Dim a As Workbook
Set a = ThisWorkbook

Set ws1 = a.Sheets("Sheet1")
Set ws2 = a.Sheets("Sheet2")
Set ws3 = a.Sheets("Sheet3")
...
Set ws9 = a.Sheets("Master")

ws1.Range("A2:C" & Range("A2").End(xlDown).Row).Copy
ws9.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

ws2.Range("A2:C" & Range("A2").End(xlDown).Row).Copy
ws9.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

ws3.Range("A2:C" & Range("A2").End(xlDown).Row).Copy
ws9.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

End Sub

Here's a screenshot of where it gets stuck:

Anyone know what the issue is and what needs tweaked?

Kind regards,
Claire

 

[NdNoviceHlp]

Hi Claire. I think your missing the bold parts below. Using Resize would be better than copy/paste. HTH. Dave
ws1.Range("A2:C" & ws1.Range("A2").End(xlDown).Row).Copy
ws9.Cells(ws9.Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

 

[clairecatx]

Hi Dave. Your advice worked beautifully. Thank you. Much appreciated!

 

[NdNoviceHlp]

You are welcome. To speed things up after each copy/paste, you should also include the line...

Application.CutCopyMode = False

To avoid copy/paste using resize...

Dim Rng As Range
Set Rng = ws1.Range("A2:C" & ws1.Range("A2").End(xlDown).Row)
ws9.Cells(ws9.Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(Rng.Rows.Count, _
            Rng.Columns.Count).Cells.Value = Rng.Cells.Value

Thanks for posting your outcome. Dave