Copy sheet from all files in folder to master

Question

I have a folder of Excel files that all have a specific sheet to copy into my master.

With Excel 2013, I need to open all files in that folder one by one and copy the specific sheet to the master file using the source file name as sheet name.

I found the following code:

Option Explicit

Sub test()

Dim wkbDest As Workbook
Dim wksDest As Worksheet
Dim wkbSource As Workbook
Dim wksSource As Worksheet
Dim MyPath As String
Dim MyFile As String

Application.ScreenUpdating = False

Set wkbDest = ThisWorkbook
Set wksDest = wkbDest.Worksheets("Sheet1") 'change the destination sheet name accordingly

MyPath = "H:\Cutover\"

If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"

MyFile = Dir(MyPath & "*.xls")

Do While Len(MyFile) > 0
    Set wkbSource = Workbooks.Open(MyPath & MyFile)
    Set wksSource = wkbSource.Worksheets("Sheet1") 'change the source sheet name accordingly
    'Your copy/paste code here (((((need help here please))))))))
    wkbSource.Close savechanges:=False
    MyFile = Dir
Loop

Application.ScreenUpdating = True

MsgBox "Completed...", vbInformation

End Sub

I managed to get to the below however it doesn't rename the sheet to the source filename.

Option Explicit

Sub test()

    Dim wkbDest As Workbook
    Dim wksDest As Worksheet
    Dim wkbSource As Workbook
    Dim wksSource As Worksheet
    Dim MyPath As String
    Dim MyFile As String
    
    Application.ScreenUpdating = False

    Set wkbDest = ThisWorkbook
    Set wksDest = wkbDest.Worksheets("Sheet1") 'change the destination sheet name accordingly

    MyPath = "H:\Cutover\"
    
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
    
    MyFile = Dir(MyPath & "*.xlsx")
    
    Do While Len(MyFile) > 0
        Set wkbSource = Workbooks.Open(MyPath & MyFile)
        Set wksSource = wkbSource.Worksheets("Sheet1") 'change the source sheet name accordingly
        Sheets("SheetToCopy").Copy Before:=Workbooks("WorkbookToPasteIn").Sheets(SheetIndex)
        wkbSource.Close savechanges:=False
        MyFile = Dir
    Loop
    
    Application.ScreenUpdating = True
    
    MsgBox "Completed...", vbInformation
    
End Sub

user8127890 · Accepted Answer · 2017-06-07 22:46:18Z

0

so the part you need help with is how to copy/paste a range from a worksheet in one file to a common worksheet in a destination file?

set up a variable to track the last empty row in the destination sheet

look up how to determine what the last row is in each source sheet, select that source range, copy it into the clipboard, and then paste it at the last empty row in the destination sheet, and reset the destination variable to the new first blank row in the destination sheet

an alternative way of doing it would be to open an output CSV file, and parse each row and build up a string and write it to the file without closing the output CSV file until the loop is over

if the files are large tho, it would be much better to use VB.NET instead as it is much faster at dealing with large files

you could also read each file/row into an in-memory datatable and then output the datatable to a CSV file, or to the destination Excel file

which method would you prefer?

answered Jun 7, 2017 at 22:46

user8127890

Sign up to request clarification or add additional context in comments.

2 Comments

GT0028 Over a year ago

the files are not big . max of 300 rows with only one sheet in them. All i need is to copy the whole sheet and paste it in the master workbook as a sheet with the source filename as sheet name. Sorry very new to VBA

GT0028 Over a year ago

@A.S.H can you help?

dominic111 · Accepted Answer · 2017-06-08 01:55:34Z

0

You could simply copy your sheet in the new workbook, following this code:

Sheets("SheetToCopy").Copy Before:=Workbooks("WorkbookToPasteIn").Sheets(SheetIndex)

And then, rename the sheet to fit your means.

answered Jun 8, 2017 at 1:55

dominic111

736 bronze badges

2 Comments

GT0028 Over a year ago

thanks fo rthis. It works however it fails after 2 workbooks saying" this name is already taken" . i need to use the file name as the sheetname .

dominic111 Over a year ago

Then immediately change the name after pasting. If you have twice the same sheet name, it will obviously not work.

Collectives™ on Stack Overflow

Copy sheet from all files in folder to master

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related