Copying named sheets from muliple files to master

scoha

Active Member
Joined
Jun 15, 2005
Messages
428
I have been trying to find code for this problem for some time and have trawled the posts to no avail - hope someone can help out there.

I have four workbooks in the same directory - all have the same structure including sheet names, just different data.

I need to regularly Copy PasteSpecial Values with Formats from a specific sheet (DataSheetName) in each workbook (ie four sheets with this name over four files) and paste the results into the master file in a single sheet with the date of this operation going into the sheet name.

The rows of data in each DataSheetName will vary between each file and from time to time. I want however to copy each successive sets of data rows below the data rows copied from the other file.

As the data in each DataSheetName is produced changes with time and data entered between updates the DataSheetName needs to be recalculated before copying.

Can anyone help me here? Thanks to ajm for trying.
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
L

Legacy 3234

Guest
Name the cell in your master sheet where you want to begin pasting something like "BeginPaste".
Code:
Sub CopyData()
set Masterbook = ActiveWorkbook
Workbooks.Open FileName:="yourpath&filename.xls"
set CopyBook = ActiveWorkbook
Sheets("SheetName1").select
Application.Goto Reference:="R2C1"  'The cell where the data you want to copy starts
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
MasterBook.Activate
Application.Goto Reference:="BeginPaste"
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

CopyBook.Activate
Sheets("SheetName2").Select
Application.Goto Reference:="R2C1"  'The cell where the data you want to copy starts
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
MasterBook.Activate
Application.Goto Reference:="BeginPaste"
'Go to end of data and down one more row
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'etc.

End Sub

Note that:
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
may not work if you have gaps in your data. If you do have gaps in your data you can use Range("A2", Range("BM65536").End(xlUp)).Copy. Or you could use that to begin with. In this example, BM is the last column with data in it. A2 is the cell where the data starts. I like to use xlToRight and xlDown if I can so I don't have to adjust the code if the number of columns ever changes.
 
L

Legacy 3234

Guest
I see I misread your questions in that you have multiple files you're copying from instead of multiple sheets. The basis for that is in here though so you can pick out what you need.
 
L

Legacy 3234

Guest
I see I misread your questions in that you have multiple files you're copying from instead of multiple sheets. The basis for that is in here thought so you can pick out what you need.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,328
Members
414,444
Latest member
lionking15

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