VBA to copy dynamic row data from multiple sheets with static column data into one sheet within the same workbook

[Chestnuttgirl98]

I've searched the forum and google and cannot find quite what i'm looking for.

I have a template for data that i need to collect from 6 individual teams within my organization. The columns are exactly the same for each team, and each team's data is represented in individual tabs/sheets. I need to combine each of the individual team's data into one master sheet (called AutoCombined). However, each team may have entered different amounts of data (rows).

How do I only copy each sheet's data (and not the header information) when the range of rows with data is dynamic? Each sheet's range will start at A2, but will end anywhere in column AD.
How do I create a macro to copy and paste each sheet's data into a master sheet, thereby having each subsequent data set paste directly after the previous one copied in order to have one continuous table of the combined data?

Current information on team data, and columns A through AD share the same header names
Team 1 has 88 rows of data
Team 2 has 92 rows of data
Team 3 has 52 rows of data
Team 4 has 53 rows of data
Team 5 has 132 rows of data
Team 6 has 28 rows of data

I need a master sheet (called AutoCombined, with headers pre-populated in row 1) with all 445 rows of data accounted for, and each time a team updates their sheet with additional data, I need to be able to automatically update the master sheet without manually copying and pasting each time.

 

[Osvaldo Palmeiro]

Hi. Try this code.
I considered that the AutoCombined sheet already exists in the file.

Sub CombineData()
 Dim ws As Worksheet, LR As Long
  Application.ScreenUpdating = False
  If Sheets("AutoCombined").[A2] <> "" Then Sheets("AutoCombined").Range("A2:AD" & Sheets("AutoCombined").Cells(Rows.Count, 1).End(3).Row) = ""
  For Each ws In ThisWorkbook.Worksheets
   If ws.Name <> "AutoCombined" Then
    LR = ws.Cells(Rows.Count, 1).End(3).Row
    ws.Range("A2:AD" & LR).Copy
    Sheets("AutoCombined").Cells(Rows.Count, 1).End(3)(2).PasteSpecial xlValues
   End If
  Next ws
  Application.ScreenUpdating = True
End Sub

 

[Chestnuttgirl98]

Hi. Try this code.
I considered that the AutoCombined sheet already exists in the file.

Sub CombineData()
Dim ws As Worksheet, LR As Long
  Application.ScreenUpdating = False
  If Sheets("AutoCombined").[A2] <> "" Then Sheets("AutoCombined").Range("A2:AD" & Sheets("AutoCombined").Cells(Rows.Count, 1).End(3).Row) = ""
  For Each ws In ThisWorkbook.Worksheets
   If ws.Name <> "AutoCombined" Then
    LR = ws.Cells(Rows.Count, 1).End(3).Row
    ws.Range("A2:AD" & LR).Copy
    Sheets("AutoCombined").Cells(Rows.Count, 1).End(3)(2).PasteSpecial xlValues
   End If
  Next ws
  Application.ScreenUpdating = True
End Sub

Thank you Osvaldo Palmerio, this is close. I copied this code and then ran it, and it copied the data from each of the individual team worksheets, then it copied and added data from sheets that I had forgotten that were hidden, and then it copied and pasted the data from the team sheets a second time. I was expecting to see ~440 lines and I ended up with 893 lines. I'm new to macros, beginner user, could you note the code so I can better follow what you are doing?

If i have additional sheets in my workbook (which i do) that i do not want data copy and pasted, how do i only activate certain sheets, copy that data, activate the next sheet, copy that data, and then paste it in the AutoCombined sheet in the next available row, and so on with the other sheets that i want to copy data from?

Thank you!!

 

[Chestnuttgirl98]

Thank you Osvaldo Palmerio, this is close. I copied this code and then ran it, and it copied the data from each of the individual team worksheets, then it copied and added data from sheets that I had forgotten that were hidden, and then it copied and pasted the data from the team sheets a second time. I was expecting to see ~440 lines and I ended up with 893 lines. I'm new to macros, beginner user, could you note the code so I can better follow what you are doing?

If i have additional sheets in my workbook (which i do) that i do not want data copy and pasted, how do i only activate certain sheets, copy that data, activate the next sheet, copy that data, and then paste it in the AutoCombined sheet in the next available row, and so on with the other sheets that i want to copy data from?

Thank you!!

Wanted to add in case it is helpful. I have the following "team" sheet names in my workbook:
Team 1
Team 2
Team 3
Team 4
Team 5
Team 6

I also have some other sheets in the workbook that are running calculations based off of some of this data. I don't want to do anything with or to those sheets. I only want to copy the dynamic row data from each of the individual team sheets (sheets 1-6) into the master sheet called "AutoCombine" so that i have a single location for all of that specific data. AutoCombine sheet exists, and row 1 is already populated with the column headers. Appreciate your help, thank you!

 

[Fluff]

How about

Sub Chestnuttgirl()
   Dim Ws As Worksheet
   
   With Sheets("AutoCombined")
      If .Range("A2") <> "" Then .Range("A2:AD" & .Range("A" & Rows.Count).End(xlUp).Row).ClearContents
      For Each Ws In Worksheets
         If Ws.Name Like "Team*" Then
            Ws.Range("A2:AD" & Ws.Range("A" & Rows.Count).End(xlUp).Row).Copy .Range("A" & Rows.Count).End(xlUp).Offset(1)
         End If
      Next Ws
   End With
End Sub

 

[Chestnuttgirl98]

How about

Sub Chestnuttgirl()
   Dim Ws As Worksheet
  
   With Sheets("AutoCombined")
      If .Range("A2") <> "" Then .Range("A2:AD" & .Range("A" & Rows.Count).End(xlUp).Row).ClearContents
      For Each Ws In Worksheets
         If Ws.Name Like "Team*" Then
            Ws.Range("A2:AD" & Ws.Range("A" & Rows.Count).End(xlUp).Row).Copy .Range("A" & Rows.Count).End(xlUp).Offset(1)
         End If
      Next Ws
   End With
End Sub

Thank you Fluff I believe I copied what I wanted correctly. I copied each IF statement, and changed the team name so i only selected the sheets I wanted to combine. I tested and ran it after each addition, and it worked. It copied what i wanted it to copy. I may need to update columns (possibility of needing 3 more types of data), and I will update "AD" to whatever columns need to be included. I may also need to add up to 3 additional team sheets, I can easily create those and add them into this code as well. Thank you!

I'm not sure I fully understand the code. I understand parts and pieces, but some of this I haven't seen before. Would you be willing to note/describe the code?

 

[Fluff]

As long as all the sheets you want to copy have a name like "Team #" then there is no need to modify the code. It will copy data from every sheet whose name starts with "Team"

 

[Chestnuttgirl98]

As long as all the sheets you want to copy have a name like "Team #" then there is no need to modify the code. It will copy data from every sheet whose name starts with "Team"

Thanks, it's working perfectly so far. If i want to pre-populate an extra column in my AutoCombine sheet and have the copied data copy into "B2", I just switch the A2's to B2's, and if i need to add some more rows above the first row in my team # sheets, I can switch those A2's to A3's...correct I'm now linking the sheets with the extra data in the workbook (that i didn't AutoCombine) to the team sheets so some of the data fields populate based on drop down choices. I may need to edit some of the positions of the existing data on the team # sheets to make the linking of some cells and equations neater/cleaner. This will most certainly impact the macro, and i want to make sure i'm accounting for those updates in the correct positions within the code. Really appreciate the help today. All of the follow on work I'm getting done because of this code is exciting!! Very glad I found this site

 

[Chestnuttgirl98]

As long as all the sheets you want to copy have a name like "Team #" then there is no need to modify the code. It will copy data from every sheet whose name starts with "Team"

Need to paste special / values only.

Ran into my first glitch. I was able to like all of my sheets the way I wanted. I updated the code to pick up the correct cells. It performed beautifully, until i noticed none of my columns with equations copied correctly. How / Where do i update the code so that when it copies and pastes to "AutoCombined" that is copies the value only from all of the cells it selects? Thank you so very much! I really do have a workhorse of a workbook

 

[Fluff]

To get values only, use

Sub Chestnuttgirl()
   Dim Ws As Worksheet
   Dim Rng As Range
   
   With Sheets("AutoCombined")
      If .Range("A2") <> "" Then .Range("A2:AD" & .Range("A" & Rows.Count).End(xlUp).Row).ClearContents
      For Each Ws In Worksheets
         If Ws.Name Like "Team*" Then
            Set Rng = Ws.Range("A2:AD" & Ws.Range("A" & Rows.Count).End(xlUp).Row)
            .Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(Rng.Rows.Count, Rng.Columns.Count).Value = Rng.Value
         End If
      Next Ws
   End With
End Sub