Combine but when Worksheet is full, start new worksheet in workbook

[Jeffman52]

In the below code, I am combining a specifically named worksheet from multiple workbooks into a single worksheet (paste special values). However there will be times when the combined data will not fit on a single worksheet and I will get an error that the "copy and paste areas are not the same size".

Can someone help with the below VBA so that if the data does not fit, it opens a new worksheet within the combined workbook and continues combining from source workbooks to the new worksheet until complete. The loop should continue until there is no data left to combine. (fills one worksheet, opens new worksheet fills new worksheet, fills that one and opens another worksheet...etc etc until all data in the folder is combined.) At the end I would have a single workbook with the possibility of multiple tabs of data from combining from multiple workbooks.

As should be obvious I am way over my head with figuring this out and any help would be appreciated.

Sub CopySheetData()
    Application.ScreenUpdating = False
    Dim MyFolder As String, MyFile As String, wkbSource As Workbook, wsDest As Worksheet, x As Long, LastRow As Long
    Set wsDest = ThisWorkbook.Sheets("Sheet1")
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Please select a folder"
        .Show
        .AllowMultiSelect = False
        If .SelectedItems.Count = 0 Then
            MsgBox "You did not select a folder."
            Exit Sub
        End If
        MyFolder = .SelectedItems(1) & "\"
    End With
    MyFile = Dir(MyFolder)
    Do While MyFile <> ""
        Set wkbSource = Workbooks.Open(Filename:=MyFolder & "\" & MyFile)
        
            
    ActiveSheet.Name = "Mi24"
    On Error Resume Next
        ActiveSheet.ShowAllData
    On Error GoTo 0
        For x = 1 To 1
       
            LastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            
            With wsDest
   ActiveSheet.UsedRange.Copy
   .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
   .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(LastRow) = wkbSource.Name
   Application.CutCopyMode = False
End With
            
        Next x
        MyFile = Dir
        wkbSource.Close False
    Loop
    Application.ScreenUpdating = True
End Sub

 

[Marc L]

Hi,​

​

you keep values rather than formulas but do you need to keep the cells formatting ?​

 

[Jeffman52]

No. the formatting is not important. I will manually format some of the columns later before I load them in Access.

 

[Marc L]

Top 2 of weird codelines from your initial code that needs some elaboration :​

​

1. For x = 1 To 1 ?​

​

2. ActiveSheet.Name = "Mi24"​

why renaming the active worksheet of the workbook that is just opened via the loop but after this workbook is closed without saving it ?!​

 

[Jeffman52]

This was someone else's code that I edited for my purposes.

For x =1 to 1 was because in the original code they were selecting sheets 1 through 3 to copy. X was the variable for the worksheet name, then the Activesheet name was Sheet'x'. I changed it to only go after a specific worksheet that is ALWAYS labeled Mi24 which made the For X=1 no longer needed.

 

[Jeffman52]

Just FYI - it took me a bit to back track my history but I got the code from this site (Mumps had posted it) and I edited it just a little

VBA - Combine Multiple workbooks into 1 worksheet

Hi, I am trying to write a VBA script which will combine multiple workbooks into 1 worksheet, copy and pasting all columns as values. Each sheet from the multiple workbooks should be pasted below the last, and ideally there would be the workbook name pasted into column A of the master as a...

www.mrexcel.com

 

[Marc L]

Other points :​

​

• according to the codeline initializing the VBA function Dir any folder choosen contains always only .xls? Excel workbooks​

'cause as it is this codeline is risky if it's not the case (then the expected workbooks extension must be hardcoded) ?​

​

• ActiveSheet is the sheet reference of a workbook opened to be combined but if the workbook contains several sheets​

and the active sheet is not the data sheet to be combined ?​

 

[Jeffman52]

Hi Marc,
Since the code has me tell it which folder the files are in, I ensure that the only thing in that folder are the files (workbooks xlsx) that I want to combine. Each Workbook have multiple worksheets but the only one I will be combining from each workbook is always named "mi24". That is why that part of the VBA is Activesheet.name = "Mi24". It forces the excel sheet to that specific worksheet

You are correct in that, if there are other things in that folder, it will try to combine those as well even if they are not excel files.

I know very little VBA. Just enough to sometimes find one that works similar to what I need and edit it a bit. But I am woefully uneducated in VBA code and know my limitation of current knowledge.

 

[Marc L]

That is why that part of the VBA is Activesheet.name = "Mi24". It forces the excel sheet to that specific worksheet

As this codeline just rename the active sheet of the opened workbook inside the loop so totally useless as the workbook is not saved …​

This codeline may crash the VBA procedure if another sheet with the same name already exists ‼​

​

So are you sure all the .xlsx workbooks of the folder have at least a sheet already named as 'Mi24' ?​

​

Since the code has me tell it which folder the files are in, I ensure that the only thing in that folder are the files (workbooks xlsx) that I want to combine.

Unclear as the code as it is just asks to select a folder but does very not check its content …​

The VBA procedure may crash in case of any wrong folder selection !​

​

 

[Jeffman52]

Its up to me to select the right folder. I don't need it to be bullet proof, just to combine the files that I ask it to combine. If I select the wrong folder or have the wrong data in that folder, that's on me not the VBA code. That I can control.

I did check the part of the code that selects the Mi24 worksheet (below) and I added a stop so I could test it. Another worksheet in the Workbook is called "Zero count" and it did indeed select the worksheet "Zero count" this time instead of Mi24 and only combined worksheets "zero count". So that is indeed where its doing the selection of the Worksheet to be combined.

 ActiveSheet.Name = "Zero Count"
    Stop

In EVERY one of my Workbooks of this folder there is a worksheet called MI24. That worksheet Mi24 is built using a very specific template so it is always identical in each Workbook as far as format goes. In that worksheet are calculations of data. Each time I complete a specific amount of work, I have had to manually cut and paste the Mi24 worksheet from each workbook into a single worksheet into a new workbook and put that into a shared folder. This VBA saves me time, and risk of error, by automating that part of the process.

I have used this VBA to combine and it works flawlessly except when I have more data to combine than will fit on a sheet and I get an error "1004 copy paste areas are not the same size". I can of course control that by putting less Workbooks into the folder at a time, but figured that maybe someone on here could help me to make it better.