Combine data from multiple workbooks into a single worksheet in a new workbook

[bp32]

I have about 100+ separate workbooks with data captured in single worksheets and I need to combine those sheets into a single worksheet in another workbook. Each sheet has the same number of columns. Essentially, I am trying of avoid copying and pasting hundreds of times.

Any advice would be much appreciated!

 

[hiker95]

bp32,

See all the available code segments in:

WBs to 1 Sheet
https://sites.google.com/a/madrocke.../merge-functions/consolidate-wbs-to-one-sheet

 

[bp32]

Thanks, but I must admit I am extremely novice when it come to VBA code. I am using a MacBook. My first glance suggested I only needed to change the following line to include the actual file path where the multiple workbooks are housed (fPath = "/Users/williampetti/Documents/Test_WorkbookCombine/" 'remember final \ in this string), but I am guessing this is far from correct.

Here is the actual code I used and it provided me with the following error: Run-time error '9': subscript out of range.

Any additional help would be most appreciated.

Option Explicit


Sub Consolidate()
'Author: Jerry Beaucaire'
'Date: 9/15/2009 (2007 compatible) (updated 4/29/2011)
'Summary: Merge files in a specific folder into one master sheet (stacked)
' Moves imported files into another folder


Dim fName As String, fPath As String, fPathDone As String
Dim LR As Long, NR As Long
Dim wbData As Workbook, wsMaster As Worksheet


'Setup
Application.ScreenUpdating = False 'speed up macro execution
Application.EnableEvents = False 'turn off other macros for now
Application.DisplayAlerts = False 'turn off system messages for now

Set wsMaster = ThisWorkbook.Sheets("Master") 'sheet report is built into


With wsMaster
If MsgBox("Clear the old data first?", vbYesNo) = vbYes Then
.UsedRange.Offset(1).EntireRow.Clear
NR = 2
Else
NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1 'appends data to existing data
End If


'Path and filename (edit this section to suit)
fPath = "/Users/williampetti/Documents/Test_WorkbookCombine/" 'remember final \ in this string
fPathDone = fPath & "Imported\" 'remember final \ in this string
On Error Resume Next
MkDir fPathDone 'creates the completed folder if missing
On Error GoTo 0
fName = Dir(fPath & "*.xls*") 'listing of desired files, edit filter as desired


'Import a sheet from found files
Do While Len(fName) > 0
If fName <> ThisWorkbook.Name Then 'don't reopen this file accidentally
Set wbData = Workbooks.Open(fPath & fName) 'Open file


'This is the section to customize, replace with your own action code as needed
LR = Range("A" & Rows.Count).End(xlUp).Row 'Find last row
Range("A1:A" & LR).EntireRow.Copy .Range("A" & NR)
wbData.Close False 'close file
NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1 'Next row
Name fPath & fName As fPathDone & fName 'move file to IMPORTED folder
End If
fName = Dir 'ready next filename
Loop
End With


ErrorExit: 'Cleanup
ActiveSheet.Columns.AutoFit
Application.DisplayAlerts = True 'turn system alerts back on
Application.EnableEvents = True 'turn other macros back on
Application.ScreenUpdating = True 'refreshes the screen
End Sub

 

[hiker95]

bp32,

I am using a MacBook.

I have no experience with a MacBook.

In the future when requesting help, please put MacBook in the title.

Click on the Reply to Thread button, and just put the word BUMP in the post. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[bp32]

bp32,



I have no experience with a MacBook.

In the future when requesting help, please put MacBook in the title.

Click on the Reply to Thread button, and just put the word BUMP in the post. Then, click on the Post Quick Reply button, and someone else will assist you.

Sorry, will definitely do that next time.

I've moved over to a PC but am still running into the same issue.

Below is the code I am using--my guess is there are some things I am missing? Any help would be greatly appreciated.

Option Explicit



Sub Consolidate()
'Author: Jerry Beaucaire'
'Date: 9/15/2009 (2007 compatible) (updated 4/29/2011)
'Summary: Merge files in a specific folder into one master sheet (stacked)
' Moves imported files into another folder




Dim fName As String, fPath As String, fPathDone As String
Dim LR As Long, NR As Long
Dim wbData As Workbook, wsMaster As Worksheet




'Setup
Application.ScreenUpdating = False 'speed up macro execution
Application.EnableEvents = False 'turn off other macros for now
Application.DisplayAlerts = False 'turn off system messages for now


Set wsMaster = ThisWorkbook.Sheets("Master") 'sheet report is built into




With wsMaster
If MsgBox("Clear the old data first?", vbYesNo) = vbYes Then
.UsedRange.Offset(1).EntireRow.Clear
NR = 2
Else
NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1 'appends data to existing data
End If




'Path and filename (edit this section to suit)
fPath = "C:\Users\Petti\Documents\New folder\" 'remember final \ in this string
fPathDone = fPath & "Imported\" 'remember final \ in this string
On Error Resume Next
MkDir fPathDone 'creates the completed folder if missing
On Error GoTo 0
fName = Dir(fPath & "*.xls*") 'listing of desired files, edit filter as desired




'Import a sheet from found files
Do While Len(fName) > 0
If fName <> ThisWorkbook.Name Then 'don't reopen this file accidentally
Set wbData = Workbooks.Open(fPath & fName) 'Open file




'This is the section to customize, replace with your own action code as needed
LR = Range("A" & Rows.Count).End(xlUp).Row 'Find last row
Range("A1:A" & LR).EntireRow.Copy .Range("A" & NR)
wbData.Close False 'close file
NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1 'Next row
Name fPath & fName As fPathDone & fName 'move file to IMPORTED folder
End If
fName = Dir 'ready next filename
Loop
End With




ErrorExit: 'Cleanup
ActiveSheet.Columns.AutoFit
Application.DisplayAlerts = True 'turn system alerts back on
Application.EnableEvents = True 'turn other macros back on
Application.ScreenUpdating = True 'refreshes the screen
End Sub

 

[hiker95]

bp32,

1. What version of Excel are you using?

2. What is the file name of the workbook that will be receiving the data from the 100+ separate workbooks?

3. What is the worksheet name that will be receiving the data from the 100+ separate workbooks?

4. What is the first cell in the worksheet that will be receiving the data?

5. Is this workbook in the same directory/folder as the 100+ separate workbooks?


6. What is the full directory structure to where the 100+ separate workbooks are stored?

7. What is the file extension of the 100+ workbooks?

8. What is the worksheet name in the 100+ workbooks?

9. What is the first row of raw data in the 100+ workbooks?

10. What is the last column being used in the 100+ workbooks?

 

[hiker95]

bp32,

In addition to the above questions:

Can I have a workbook containing 2 worksheets?

The first worksheet will be the worksheet that will be receiving the raw data from the 100+ separate workbooks.

The second worksheet will be a copy of one of the 100+ separate workbooks worksheets raw data.

You can upload your workbook to Box Net,
sensitive data scrubbed/removed/changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[jbeaucaire]

BP32 - are you still trying to get my macro working for you?

The error "subscript out of range" means the macro tried to find or use something by a specific name that if could not find. Sheet name errors, or workbook name errors, typically.


Did you enter the fPATH into the macro?

When you get the error, click on DEBUG and tell me which line of code is giving the error.
On that line of code hover your mouse over any variables to see what the value of the variable is at this moment of error.

 

[bjay_tiamsic26]

how can i specify to what cell should the macro start copying?

 

[jbeaucaire]

IN the macro are comments. The section called "this is the section to customize" is where data is being copied. It's currently copy from A1:A?? where ?? is the LastRow of data it spots. You can adjust the 1 to whatever you want the first row to be.