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

Jeffman52

New Member
Joined
May 14, 2021
Messages
12
Office Version
  1. 2010
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,151
Office Version
  1. 2010
Platform
  1. Windows
Hi,​
you keep values rather than formulas but do you need to keep the cells formatting ?​
 

Jeffman52

New Member
Joined
May 14, 2021
Messages
12
Office Version
  1. 2010
Platform
  1. Windows
No. the formatting is not important. I will manually format some of the columns later before I load them in Access.
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,151
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
May 14, 2021
Messages
12
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
May 14, 2021
Messages
12
Office Version
  1. 2010
Platform
  1. Windows
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

 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,151
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
May 14, 2021
Messages
12
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Apr 5, 2021
Messages
1,151
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
May 14, 2021
Messages
12
Office Version
  1. 2010
Platform
  1. Windows
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.


VBA Code:
 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.
 

Forum statistics

Threads
1,137,351
Messages
5,680,988
Members
419,948
Latest member
Sbakker1

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