Copying named sheets from muliple files to master

[scoha]

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.

 

[Legacy 3234]

Name the cell in your master sheet where you want to begin pasting something like "BeginPaste".

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.

 

[Legacy 3234]

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.

 

[Legacy 3234]

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.