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

Chestnuttgirl98

New Member
Joined
Apr 18, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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.
 

Chestnuttgirl98

New Member
Joined
Apr 18, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
To get values only, use
VBA Code:
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

Honest to goodness, can't thank you enough for helping me. Very much appreciated!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Forum statistics

Threads
1,141,068
Messages
5,704,093
Members
421,327
Latest member
Msh

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