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" <(E-Mail Removed)> wrote in message news:eca5c476-6e00-4cdb-9d06-(E-Mail Removed)...
> 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" <rondebr...@kabelfoon.nl> wrote:
> See alsohttp://www.rondebruin.nl/copy3.htm
>
> --
>
> Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm
>
>
>
> "Sheldon Potolsky" <SHPsalm...@aol.com> wrote in messagenews:eca5c476-6e00-4cdb-9d06-(E-Mail Removed)...
> > 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 <J...@discussions.microsoft.com> 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 -

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

On May 9, 10:16*am, "Ron de Bruin" <rondebr...@kabelfoon.nl> wrote:
> See alsohttp://www.rondebruin.nl/copy3.htm
>
> --
>
> Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm
>
>
>
> "Sheldon Potolsky" <SHPsalm...@aol.com> wrote in messagenews:eca5c476-6e00-4cdb-9d06-(E-Mail Removed)...
> > 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 -

You not copy all the code

Download the example workbook and test it

--

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


"Sheldon Potolsky" <(E-Mail Removed)> wrote in message news:00941541-b389-44df-8081-(E-Mail Removed)...
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

On May 9, 10:16 am, "Ron de Bruin" <rondebr...@kabelfoon.nl> wrote:
> See alsohttp://www.rondebruin.nl/copy3.htm
>
> --
>
> Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm
>
>
>
> "Sheldon Potolsky" <SHPsalm...@aol.com> wrote in messagenews:eca5c476-6e00-4cdb-9d06-(E-Mail Removed)...
> > 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 -

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.

"Ron de Bruin" wrote:

> You not copy all the code
>
> Download the example workbook and test it
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "Sheldon Potolsky" <(E-Mail Removed)> wrote in message news:00941541-b389-44df-8081-(E-Mail Removed)...
> 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
>
> On May 9, 10:16 am, "Ron de Bruin" <rondebr...@kabelfoon.nl> wrote:
> > See alsohttp://www.rondebruin.nl/copy3.htm
> >
> > --
> >
> > Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm
> >
> >
> >
> > "Sheldon Potolsky" <SHPsalm...@aol.com> wrote in messagenews:eca5c476-6e00-4cdb-9d06-(E-Mail Removed)...
> > > 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 -
>
>