Joel
Sat May 10 08:00:00 CDT 2008
Should be
newbk.Rows("1:" & NewbkLastRow).Copy _
Destination:=oldbkSht.Rows(OldbkLastRow + 1)
The source and destination must be consistant. In this case both rows. You
can't mix Rows and Range like Norman suggested.
"Norman Jones" wrote:
> Hi Flounder73,
>
> I suspect that
>
> > newbk.Rows("A1:E" & NewbkLastRow).Copy _
> > Destination:=oldbkSht.Row(OldbkLastRow + 1)
>
> was intended as
>
> newbk.Range("A1:E" & NewbkLastRow).Copy _
> Destination:=oldbkSht.Row(OldbkLastRow + 1)
>
> Ron de Bruin has an extensive set of sample
> routines for copying and merging data from
> multiple files at:
>
> Copy/Paste/Merge examples
>
http://www.rondebruin.nl/tips.htm
>
> Given the large number of files cited in your
> question, you may wish to consider Ron's
> techniques for copying data from closed files.
> See:
>
> Copy a range from closed workbooks (ADO)
>
http://www.rondebruin.nl/ado.htm
>
> Ron also offers his RDBMerge Add-in which
> enables the merge operation to be performed
> with a few mouse clicks. |Details of the Add-in
> and a download link may be found at
>
> RDBMerge Add-in
>
http://www.rondebruin.nl/merge.htm
>
>
>
> ---
> Regards.
> Norman
>
>
> "flounder73" <flounder73@discussions.microsoft.com> wrote in message
> news:F013EE2C-C70C-49CA-A4AE-4C88D9D64E94@microsoft.com...
> > Hi Joel,
> >
> > Thanks for code...however, I'm having an issue with a part of the code
> > "newbk.Rows("A1:E" & NewbkLastRow).Copy _
> > Destination:=oldbkSht.Row(OldbkLastRow + 1)"
> > in that it has a compilation error saying the function is not supported.
> > I
> > also made a mistake in my post and I need to copy rows 1 - 5 not columns.
> >
> > Sorry...thanks in advance.
> >
> > "Joel" wrote:
> >
> >>
> >> Folder = "c:\temp"
> >> FName = Dir(Folder & "\*.xls")
> >> Set oldbkSht = ThisWorkbook.Sheets("Sheet1")
> >> Do While FName <> ""
> >> OldbkLastRow = oldbkSht.Range("A" & Rows.Count).End(xlUp)
> >> Set newbk = Workbooks.Open(Filename:=Folder & "\" & FName)
> >> Set newbkSht = newbk.Sheets("Sheet1")
> >> NewbkLastRow = newbkSht.Range("A" & Rows.Count).End(xlUp)
> >> newbk.Rows("A1:E" & NewbkLastRow).Copy _
> >> Destination:=oldbkSht.Row(OldbkLastRow + 1)
> >> newbk.Close
> >> FName = Dir()
> >> Loop
> >>
> >> "flounder73" wrote:
> >>
> >> > Hi All,
> >> >
> >> > I'm going crazy try to copy and paste data from 3000 files into 1 file.
> >> > Is
> >> > there a way to automatically open a file, copy columns 1 -5 and then
> >> > append
> >> > it to the file where I want it all placed?
> >> >
> >> > Thanks in advance...
> >> >
> >> >
>
>