Appending Contents Of Multiple Spreadsheets Into One

[Sheldon Potolsky]

Hello. I would like to open up a new spreadsheet and append rows 2 -
EOF from six other spreadsheets into that new one, be prompted for the
name to save it with, then save and quit. Would someone be kind
enough to offer some VBA code to do this with?
Thank you,
Sheldon Potolsky

 

[Joel]

Set Sumsht = Worksheets.Add(after:=Sheets(Sheets.Count))
Sumsht.Name = "Summary"
For Each sht In ThisWorkbook.Sheets
If sht.Name <> Sumsht.Name Then
SumLastRow = Sumsht.Range("A" & Rows.Count).End(xlUp).Row
shtLastRow = sht.Range("A" & Rows.Count).End(xlUp).Row
sht.Rows("2:" & shtLastRow).Copy _
Destination:=Sumsht.Rows(SumLastRow + 1)
End If
Next sht

 

[Joel]

I forgot the SAVEAS

Set Sumsht = Worksheets.Add(after:=Sheets(Sheets.Count))
Sumsht.Name = "Summary"
For Each sht In ThisWorkbook.Sheets
If sht.Name <> Sumsht.Name Then
SumLastRow = Sumsht.Range("A" & Rows.Count).End(xlUp).Row
shtLastRow = sht.Range("A" & Rows.Count).End(xlUp).Row
sht.Rows("2:" & shtLastRow).Copy _
Destination:=Sumsht.Rows(SumLastRow + 1)
End If
Next sht

FName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If fileSaveName <> False Then
ThisWorkbook.SaveAs Filename:=FName
ThisWorkbook.Close
End If

 

[Ron de Bruin]

See also
http://www.rondebruin.nl/copy3.htm

 

[Sheldon Potolsky]

Thank you Ron and Joel for the code to do this.
Sheldon

 

[Sheldon Potolsky]

Joel,
I tried running the code you sent and realized that I may not have
been clear on a couple of things.
I actually wanted to append rows 2-EOF from other spreadsheets (and be
prompted for them), and save them in Sheet1 sheet in the blank
spreadsheet I just opened. So, if in my C:\Excel folder I have
Sheldon1.xls, Sheldon2.xls, Sheldon3.xls and Joel1.xls, I would reply
to a prompt for which spreadsheets to include. My response would be
Sheldon*.xls. Rows 2-EOF for all three spreadsheets would be appended
to Sheet1 in my blank spreadsheet. After that your SAVEAS section of
the code would run, I'd select a filename and the new spreadsheet
would be saved and the program would complete.
Thanks, Sheldon

 

[Sheldon Potolsky]

Hi Ron. I tried your code (below) but got:
Compile error: Sub or Function not defined (with Get_File_Names
highlighted)
Am I missing something?
Thanks, Sheldon
*****************************
Sub RDB_Merge_Data()
Dim myFiles As Variant
Dim myCountOfFiles As Long

myCountOfFiles = Get_File_Names( _
MyPath:="C:\Users\Ron\test", _
Subfolders:=False, _
ExtStr:="*.xl*", _
myReturnedFiles:=myFiles)

If myCountOfFiles = 0 Then
MsgBox "No files that match the ExtStr in this folder"
Exit Sub
End If

Get_Data _
FileNameInA:=True, _
PasteAsValues:=True, _
SourceShName:="", _
SourceShIndex:=1, _
SourceRng:="A1:G1", _
StartCell:="", _
myReturnedFiles:=myFiles

End Sub

 

[Ron de Bruin]

You not copy all the code

Download the example workbook and test it

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


Hi Ron. I tried your code (below) but got:
Compile error: Sub or Function not defined (with Get_File_Names
highlighted)
Am I missing something?
Thanks, Sheldon
*****************************
Sub RDB_Merge_Data()
Dim myFiles As Variant
Dim myCountOfFiles As Long

myCountOfFiles = Get_File_Names( _
MyPath:="C:\Users\Ron\test", _
Subfolders:=False, _
ExtStr:="*.xl*", _
myReturnedFiles:=myFiles)

If myCountOfFiles = 0 Then
MsgBox "No files that match the ExtStr in this folder"
Exit Sub
End If

Get_Data _
FileNameInA:=True, _
PasteAsValues:=True, _
SourceShName:="", _
SourceShIndex:=1, _
SourceRng:="A1:G1", _
StartCell:="", _
myReturnedFiles:=myFiles

End Sub

 

[ACarella]

Hi Ron:
I was wondering if you could help me.

I have the following in Excel 2007:
w8001.xlsx
w346.xlsx
w78.xlsx
w172.xlsx

w8001.xlsx has 8 columns and 8001 rows of data
w346.xlsx has 3 columns and 346 rows of data
w78.xlsx has 3 columns and 78 rows of data
w172.xlsx has 3 columns and 172 rows of data

I need to append the three columns (A, B and C) of w346, 278 and w172 to the
END of W8001 and the data to append to the appropriate 3 columns.
How do I proceed.