0

I have been trying to make a simple loop that cycles through a group of worksheets that contain data and paste them onto a master sheet. The amount of sheets can vary depending on how many people use it (1 sheet per user) and the amount of rows can too (depending on how much work is allocated), however the amount of columns will remain the same. Essentially I'm trying to transfer whatever is populated on user's sheets on to a master for reporting purposes. I have been trying to piece together bits of code without success

Public Sub moveData()
    Dim wsCount As Integer
    Dim I As Integer

    wsCount = ActiveWorkbook.Worksheets.Count

    For I = 1 To wsCount
        Worksheets(I).Activate
        Range("A2:O4").Select
        Application.Selection.Copy
        Sheets("Master").Activate
        ActiveSheet.Range("A2").End(xlDown).Offset(1, 0).Select 
        Selection.PasteSpecial Paste:=xlPasteValues
    Next I
End Sub

However, whenever its ran it copies the master too. Is there a way of getting it to ignore the master and just paste into it? Thank you

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Dr Hoo
  • 33
  • 5
  • 1
    `If Worksheets(I).Name <> "Master" Then` - add after `For I = 1 To wsCount`. Then add an `End If` before `Next I`. – BigBen Aug 07 '19 at 17:54
  • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Aug 08 '19 at 06:36
  • Hi BigBen, I tried that but must have been entering it in the wrong place. Thank you – Dr Hoo Aug 08 '19 at 09:13

1 Answers1

0

If the master sheet is your first sheet, you can change your for loop to start at the second sheet.

Your code would now be:

Public Sub moveData()

Dim wsCount As Integer
Dim I As Integer

wsCount = ActiveWorkbook.Worksheets.Count

For I = 2 To wsCount
    Worksheets(I).Activate
    CurrLastRow = Sheets(i).Cells(Rows.Count, 1).End(xlUp).Row 'This will find the last row on the current sheet
    Sheets(I).Range("A2:O" & CurrLastRow).Copy
    Sheets("Master").Activate
    Sheets("Master").Range("A2").End(xlDown).Offset(1, 0).Select 
    Selection.PasteSpecial Paste:=xlPasteValues
Next I

End Sub

If your master sheet is not the first sheet, I suggest moving it there in order to use the above code.

abbsichel
  • 156
  • 1
  • 11