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

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

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

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

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 -

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 -