Copy worksheet to another workbook
 

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.

Copy worksheet to another workbook
 

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

--
Regards,
Tom Ogilvy


"Dave" wrote in message
...
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.

Copy worksheet to another workbook
 

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" wrote:

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

--
Regards,
Tom Ogilvy


"Dave" wrote in message
...
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.

Copy worksheet to another workbook
 

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.

--
Regards,
Tom Ogilvy



"Dave" wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy


"Dave" wrote in message
...
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.

Copy worksheet to another workbook
 

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" wrote:

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.

--
Regards,
Tom Ogilvy



"Dave" wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy


"Dave" wrote in message
...
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.

Copy worksheet to another workbook
 

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

--
Regards,
Tom Ogilvy

"Dave" wrote in message
...
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" wrote:

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.

--
Regards,
Tom Ogilvy



"Dave" wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy


"Dave" wrote in message
...
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.

Copy worksheet to another workbook
 

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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"Dave" wrote in message
...
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" wrote:

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.

--
Regards,
Tom Ogilvy



"Dave" wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy


"Dave" wrote in message
...
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.