Bring in data from specific tabs to one data tab

[mnadams]

Hello,
Any assistance anyone can provide would be great.

I have a workbook in which I need data from 5 specific tabs pulled in to one data tab within the same workbook.

The data destination tab is named "data"
and the tabs that I need data from are named:

1. NA
2. EC
3. ME
4. VJ
5. TK

After I get all data in the "data" tab, I plan to create a pivot table based on the data. I have tried several attempts at the code but I am running in to errors when trying to call out specific tabs versus pulling in data from "all" tabs.

Thank you in advance!

 

[VoG]

Try

Sub atest()
Dim ws As Worksheet
For Each ws In Sheets(Array("NA", "EC", "ME", "VJ", "TK"))
    ws.UsedRange.Copy Destination:=Sheets("Data").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next ws
End Sub

 

[mnadams]

This was great, but need small modification...
it's bringing in the header rows on each tab. Any suggestions?

 

[VoG]

Try

Sub atest()
Dim ws As Worksheet
For Each ws In Sheets(Array("NA", "EC", "ME", "VJ", "TK"))
    ws.UsedRange.Offset(1).Copy Destination:=Sheets("Data").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next ws
End Sub

 

[mnadams]

This was great! Thank you so much. I was able to add additional macros to accomplish the final result. Very appreciative!

 

[pedro-egoli]

Wonder if you can help please.

In trying to get a test sheet for my own use I used a tab named "summary" and two other tabs named "Jun" and "Jul".
Then modified code to

Sub atest()
Dim ws As Worksheet
For Each ws In Sheets(Array("Jun", "Jul",))
    ws.UsedRange.Copy Destination:=Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next ws
End Sub

This produced a syntax error when I ran the macro, with "Sub atest" being in yellow and
"For Each ws In Sheets(Array("Jun", "Jul",))" being highlighted.

Any assistance appreciated.

Pedro

 

[VoG]

You had an extraneous comma. It should be

Sub atest()
Dim ws As Worksheet
For Each ws In Sheets(Array("Jun", "Jul"))
    ws.UsedRange.Copy Destination:=Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next ws
End Sub

 

[pedro-egoli]

Thanks Vog,
It worked but not exactly how I thought it would.

In tabs "Jun" and July I had data in cells B2:B4.
When macro was run the data in Summary Sheet was
A2:A4 the figures from Jun tab
A5:A7 the figures from Jul tab

Is it possible to tweak the macro so that info goes into Summary sheet in the same rows as that on individual tabs, but in different columns which have the Tab name in Row 1 on each sheet.

So in Summary sheet column A has the name "Jun" in A1 and all the data in "Jun" tab cells B2:B100 transfers to Summary sheet A2:A100.
Then in Summary sheet column B has the name "Jul" in B1 and all the info in tab named "Jul" transfers to Summary sheet B2:B100.
This continues until Summary Sheet has 12 columns with rows 2:100 containing the individual tab's data.
In other words the summary sheet would contain a snapshot of the 12 individual tabs.

It might be necessary to quarantine Column A to enable individual data items to be identified and the resultant data from each tab inserted in Columns B:M .

Please don't go to too much trouble with this.

Pedro

 

[VoG]

Maybe like this

Sub atest()
Dim ws As Worksheet, j As Long
For Each ws In Sheets(Array("Jun", "Jul"))
    j = j + 1
    ws.UsedRange.Copy Destination:=Sheets("Summary").Cells(Rows.Count, j).End(xlUp).Offset(1)
Next ws
End Sub

 

[pedro-egoli]

Thats pretty close.
Only problem is that as i add a month and then run macro it duplicates original data in lower cells.
Is there a way to clear any previous data before the transfer ?
If not I can easily delete it manually.

Thanks
Pedro