How to copy rows from multiple tabs into one master sheet

MrRosco

New Member
Joined
May 12, 2016
Messages
47
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:confused::)
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Caleeco

Well-known Member
Joined
Jan 9, 2016
Messages
980
Office Version
  1. 2010
Platform
  1. Windows
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 :)
 
Upvote 0

MrRosco

New Member
Joined
May 12, 2016
Messages
47
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 :)
 
Upvote 0

MrRosco

New Member
Joined
May 12, 2016
Messages
47
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 :)
 
Upvote 0

Caleeco

Well-known Member
Joined
Jan 9, 2016
Messages
980
Office Version
  1. 2010
Platform
  1. Windows
No problem, Ive done a bit of testing and the below code seems to work.

Code:
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
 
Upvote 0

MrRosco

New Member
Joined
May 12, 2016
Messages
47
No problem, Ive done a bit of testing and the below code seems to work.

Code:
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
 
Upvote 0

MrRosco

New Member
Joined
May 12, 2016
Messages
47
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.
 
Upvote 0

Caleeco

Well-known Member
Joined
Jan 9, 2016
Messages
980
Office Version
  1. 2010
Platform
  1. Windows
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
 
Last edited:
Upvote 0

MrRosco

New Member
Joined
May 12, 2016
Messages
47
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
 
Upvote 0

Forum statistics

Threads
1,186,426
Messages
5,957,765
Members
438,319
Latest member
Girlboss3036

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