How to copy rows from multiple tabs into one master sheet

[MrRosco]

Hi All,

First post on the site so please bare with

Im creating a document which is going to be forecasting sales for different products and to save time and not have to manually copy and paste i would like to know if its possible if a macro/vba code could achieve this obviously i don't wan the data to overlap.

also i may be adding more tabs in the future if that makes any difference.

my data input will start from cell A6 and finishing in cell H6

so far i have seven tabs

Thanks in advance for your help

Rosco

 

[Caleeco]

Hello Rosco,

Yes that's fairly straightforward to do. A few questions though.

Is the Mastersheet always going to be Sheet1 of the workbook?
Does the mastersheet have header rows, if so are they in row 1?
Are there any special formats? dates, currency in the cells A6:H6?

Let me know and i can whip up some code
Caleeco

 

[MrRosco]

Thanks for the quick reply!

rows A5:H5 are header names

A:E Will contain a VLOOKUP for for account name code etc and F:H will be a monetary value which will be a manual input.

The Mastersheet will have the same header names and will start from the same position as the individual product tabs.

The Mastersheet is called "Mastersheet" if that helps. once it all gets populated i will be using the data from the master sheet to create pivot tables/charts etc

Thanks again
Ross

 

[MrRosco]

Hello Rosco,

Yes that's fairly straightforward to do. A few questions though.

Is the Mastersheet always going to be Sheet1 of the workbook?
Does the mastersheet have header rows, if so are they in row 1?
Are there any special formats? dates, currency in the cells A6:H6?

Let me know and i can whip up some code
Caleeco

Thanks for the quick reply!

rows A5:H5 are header names

A:E Will contain a VLOOKUP for for account name code etc and F:H will be a monetary value which will be a manual input.

The Mastersheet will have the same header names and will start from the same position as the individual product tabs.

The Mastersheet is called "Mastersheet" if that helps. once it all gets populated i will be using the data from the master sheet to create pivot tables/charts etc

Thanks again
Ross

 

[Caleeco]

No problem, Ive done a bit of testing and the below code seems to work.

Option Explicit
Sub CopySheets()
Dim SheetCount As Long
Dim Sh As Worksheet
Dim i As Long
Dim lrow As Long
SheetCount = Worksheets.Count
For i = 1 To SheetCount
    If Worksheets(i).Name = "Mastersheet" Then
        'Do Nothing
    Else
        Worksheets(i).Range("A6", "H6").Copy
        lrow = Worksheets("Mastersheet").Range("A" & Rows.Count).End(xlUp).Row
        Worksheets("Mastersheet").Range("A" & lrow).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    End If
Next i
End Sub

Please try it on your data and let me know if it does what you need.

Many Thanks
Caleeco

 

[MrRosco]

No problem, Ive done a bit of testing and the below code seems to work.

Option Explicit
Sub CopySheets()
Dim SheetCount As Long
Dim Sh As Worksheet
Dim i As Long
Dim lrow As Long
SheetCount = Worksheets.Count
For i = 1 To SheetCount
    If Worksheets(i).Name = "Mastersheet" Then
        'Do Nothing
    Else
        Worksheets(i).Range("A6", "H6").Copy
        lrow = Worksheets("Mastersheet").Range("A" & Rows.Count).End(xlUp).Row
        Worksheets("Mastersheet").Range("A" & lrow).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    End If
Next i
End Sub

Please try it on your data and let me know if it does what you need.

Many Thanks
Caleeco

Thanks for getting back to me.

quick question, do i need to copy this into every single tab, second what do i need to do to refresh the data on the master sheet?

Thanks
Rosco

 

[MrRosco]

Thanks for getting back to me.

quick question, do i need to copy this into every single tab, second what do i need to do to refresh the data on the master sheet?

Thanks
Rosco

could i send you the file to see what i mean, as the document will be constantly updated.

 

[Caleeco]

Have you used the VBA Editor before? You need to do the following

1. using your Keyboard, press CTRL + F11
2. Right Click the VBAProject > Insert Module > Paste the code in the window that pops up
You can then either set up a macro button in the Mastersheet, or run it from the VBA Editor by hitting "F5"

Having thought about your requirements. is there any existing data below row 5 on the mastersheet? Also, when you run the macro... do you want the data to be replaced to just added to the bottom of the table each time?

You could add the file to a dropbox, or use some of the forum tools to add an excerpt.
http://www.mrexcel.com/forum/about-board/508133-attachments.html

Caleeco

 

[MrRosco]

Hi Caleeco,

Yes their will be a lot of data eventually as the document will be updated with foretasted sales, that is why i want it to all be consolidated onto one **** which i can then pivot

here is the link https://www.dropbox.com/s/ybdvj8dhm6u7dyi/Book1.xlsx?dl=0

no data is on their at the moment just a basic shell

 

[MrRosco]

Have you used the VBA Editor before? You need to do the following

1. using your Keyboard, press CTRL + F11
2. Right Click the VBAProject > Insert Module > Paste the code in the window that pops up
You can then either set up a macro button in the Mastersheet, or run it from the VBA Editor by hitting "F5"

Having thought about your requirements. is there any existing data below row 5 on the mastersheet? Also, when you run the macro... do you want the data to be replaced to just added to the bottom of the table each time?

You could add the file to a dropbox, or use some of the forum tools to add an excerpt.
http://www.mrexcel.com/forum/about-board/508133-attachments.html

Caleeco

Hi Caleeco,

Yes their will be a lot of data eventually as the document will be updated with foretasted sales, that is why i want it to all be consolidated onto one **** which i can then pivot

here is the link https://www.dropbox.com/s/ybdvj8dhm6...ook1.xlsx?dl=0

no data is on their at the moment just a basic shell