Copy worksheet to another workbook

[Guest]

I'm fairly new to VBA and require help please.
I want to do the the following,
From an open workbook (Source) C:\All Weather\AWResults.xls\Myrunners\Range
("A2:N"), copy the data from this workbook to the last row in the
(Destination) workbook namely C:\All Weather\My Runners\Sheet1\Range("A1").
Any help would be appreciated.

 

[Tom Ogilvy]

Sub CopyData()
Dim bksrc as Workbook
Dim bkdest as Workbook
on error resume next
set bksrc = Workbooks("AWResults.xls")
On error goto 0
if bksrc is nothing then
set bksrc = workbooks.Open("C:\All Weather\AWResults.xls")
end if
on Error Resume Next
set bkDest = Workbooks("My Runners.xls")
On Error goto 0
if bkdest is nothing then
set bkdest = workbooks.Open("C:\All Weather\My Runners.xls")
End if
bksrc.worksheets("MyRunners").Range("A2:N2").copy _
Destination:=bkdest.Worksheets("Sheet1").Range("A1")
End Sub

 

[Guest]

Hi Tom,
Thanks very much for your help its very much appreciated.
Your program works great !!! just what I was looking for.
Can I impose on you again and ask if you can include in
the program, coding, so that it will always copy data to the
next empty row down in the destination sheet.
Thanks once again.
Regards Dave

 

[Tom Ogilvy]

Sub CopyData()
Dim bksrc as Workbook
Dim bkdest as Workbook
on error resume next
set bksrc = Workbooks("AWResults.xls")
On error goto 0
if bksrc is nothing then
set bksrc = workbooks.Open("C:\All Weather\AWResults.xls")
end if
on Error Resume Next
set bkDest = Workbooks("My Runners.xls")
On Error goto 0
if bkdest is nothing then
set bkdest = workbooks.Open("C:\All Weather\My Runners.xls")
End if
bksrc.worksheets("MyRunners").Range("A2:N2").copy _
Destination:=bkdest.Worksheets("Sheet1")
.cells(rows.count,1).End(xlup)(2)
End Sub

This will skip the first row - this could be fixed with some extra code if
it is a biggie.

 

[Guest]

Hi Tom,
Thanks yet again for your help, that works great!!!
Just one other thing, in the (Source) worksheet where the range
is (A2:N2) this can go up to as high as (A2: N300) in a day, can the coding
be adjusted so that it will always copy the data up to the last cell
in Column "N".
Again thank you for your help very much appreciated.
Regards
Dave

 

[Tom Ogilvy]

Sub CopyData()
Dim bksrc as Workbook
Dim bkdest as Workbook
Dim rng as Range
on error resume next
set bksrc = Workbooks("AWResults.xls")
On error goto 0
if bksrc is nothing then
set bksrc = workbooks.Open("C:\All Weather\AWResults.xls")
end if
on Error Resume Next
set bkDest = Workbooks("My Runners.xls")
On Error goto 0
if bkdest is nothing then
set bkdest = workbooks.Open("C:\All Weather\My Runners.xls")
End if
with bksrc.worksheets("MyRunners")
set rng = .cells(rows.count,"N").End(xlup)
set rng = .Range("A1",rng)
End With
rng.copy Destination:=bkdest.Worksheets("Sheet1") _
.cells(rows.count,1).End(xlup)(2)
End Sub

 

[Guest]

Hi Tom
Needless to say that works perfectly, thanks again for all of your help
you are a champ.
Regards
Dave