Appending Contents Of Multiple Spreadsheets Into One
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 |
Appending Contents Of Multiple Spreadsheets Into One
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 "Sheldon Potolsky" wrote: 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 |
Appending Contents Of Multiple Spreadsheets Into One
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 "Sheldon Potolsky" wrote: 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 |
Appending Contents Of Multiple Spreadsheets Into One
See also
http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sheldon Potolsky" wrote in message ... 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 |
Appending Contents Of Multiple Spreadsheets Into One
Thank you Ron and Joel for the code to do this.
Sheldon On May 9, 10:16*am, "Ron de Bruin" wrote: See alsohttp://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Sheldon Potolsky" wrote in ... 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- Hide quoted text - - Hide quoted text - |
Appending Contents Of Multiple Spreadsheets Into One
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 On May 9, 9:10*am, Joel wrote: 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 "Sheldon Potolsky" wrote: 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- Hide quoted text - - Show quoted text - |
All times are GMT +1. The time now is 09:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com