VBA Code to copy data from multiple worksheets and paste into single "Combined" worksheet

nshepo20

New Member
Joined
Jun 8, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Hello, I need help with how my VBA code works. I am trying to copy data from multiple worksheets (variable number and names) into a single "Combined" worksheet. This is proving more difficult because there are hidden worksheets that I can't change and "Template" sheet only has one row of data so using "Range(Selection, Selection.End(xlDown)).Copy" for that worksheet wouldn't work... Thus I have my code doing the "Template" sheet first and then doing the loop, however my code only does it on the "Combined" sheet and ignore my <> "Combined".

Can someone explain why this doesn't work and potentially help me fix it. Thank you!!

PS

I think it has something to do with using select? But I am trying to learn how to avoid using it so any tips on that end would be really appreciated... :)

VBA Code:
Sub CP2Combined()

Dim Current As Worksheet

        Sheets("Template").Range("C2:AH2").Copy
        Sheets("Combined").Range("B3").Select
        ActiveSheet.Paste
       
For Each Current In Worksheets
   
     If Current.Visible = xlSheetVisible Then
     If Current.Name <> "Combined" And _
        Current.Name <> "Template" Then
            Range("C2:AH2").Select
            Range(Selection, Selection.End(xlDown)).Copy
            Sheets("Combined").Select
            Range("B" & Rows.Count).End(xlUp).Offset(1).Select
            ActiveSheet.Paste
      End If
      End If
Next


End Sub
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,548
Office Version
  1. 365
Platform
  1. Windows
Try
VBA Code:
Sub CP2Combined()

Dim Current As Worksheet

        Sheets("Template").Range("C2:AH2").Copy
        Sheets("Combined").Range("B3").Select
        ActiveSheet.Paste
        
For Each Current In Worksheets
    
     If Current.Visible = xlSheetVisible Then
     If Current.Name <> "Combined" And _
        Current.Name <> "Template" Then
            With Current
               .Range("C2:AH" & .Range("C" & Rows.Count).End(xlUp).Row).Copy Sheets("Combined").Range("B" & Rows.Count).End(xlUp).Offset(1)
            End With
      End If
      End If
Next


End Sub
 
Solution

nshepo20

New Member
Joined
Jun 8, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Try
VBA Code:
Sub CP2Combined()

Dim Current As Worksheet

        Sheets("Template").Range("C2:AH2").Copy
        Sheets("Combined").Range("B3").Select
        ActiveSheet.Paste
       
For Each Current In Worksheets
   
     If Current.Visible = xlSheetVisible Then
     If Current.Name <> "Combined" And _
        Current.Name <> "Template" Then
            With Current
               .Range("C2:AH" & .Range("C" & Rows.Count).End(xlUp).Row).Copy Sheets("Combined").Range("B" & Rows.Count).End(xlUp).Offset(1)
            End With
      End If
      End If
Next


End Sub
Thank you so much it works! <3 I spent way too much time trying to figure that out lol
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
Alternatively, slightly different code but similar end result (I hope!), try:
VBA Code:
Sub Combine_Sheets()
  
    Dim ignore_sheets As Variant
    Dim x As Long
    Dim c As Long
  
    ignore_sheets = Valid_Sheet_Names
    c = Range("C2:AH2").Columns.Count
  
    Application.ScreenUpdating = False
  
    With Sheets("Combined")
        'Clear master sheet and add template
        x = .cells(.Rows.Count, 2).End(xlUp).Row
        With .[B3]
            .Resize(x, c).value = ""
            .Resize(, c).Value = Sheets("Template").Cells(2, 3).Resize(, c).Value
        End With
  
        'Combine sheets
        For x = 1 To Worksheets.Count
             With Sheets(x)
                If InStr(ignore_sheets, .name) = 0 Then Add_Data Sheets(x), c
            End With
        Next x
                  
        'Set focus to Combined
        If ActiveSheet.name <> .name Then .Activate
    End With
 
    Application.ScreenUpdating = True
  
    Erase ignore_sheets

End Sub

Private Function Valid_Sheet_Names() As String
  
    Valid_Sheet_Names = "Combined|Template"
  
End Function

Private Sub Add_Data(wks As Worksheet, c As Long)

    Dim x As Long
  
    With wks
        x = .Cells(.Rows.Count, 3).End(xlUp).Row - 1
        If x > 0 Then Sheets("Combined").Cells(Rows.Count, 2).End(xlUp).Offset(1).Resize(x, c).Value = .Cells(2, 3).Resize(x, c).Value
    End With
  
End Sub
 

Forum statistics

Threads
1,141,619
Messages
5,707,449
Members
421,509
Latest member
kt_mr_excel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top