Exporting to another excel file
 

I have tried this code but I get an error message

Run-time error '9'
Subscript out of range

Can you advise please.

Thank you

"Tom Ogilvy" wrote:

Dim bk as workbook
Dim bSave as Boolean

' test to see if Destination.xls is already open

On error resume next
set bk = Workbooks("Destination.xls")
On error goto 0
if bk is nothing then
bSave = True
set bk = workbooks.open("C:\Destination.xls"
End if
ThisWorkbook.Sheets("Sheet1").Range("A1:C3").copy _
destination:=bk.Worksheets("Test").Range("M5")

' if destination was originally closed, then save and close it

if bSave then bk.Close Savechanges:=True

--
regards,
Tom Ogilvy


Sheet/Location:
Test!M5


"Chad" wrote:

This may seem like a simple question but I am not sure how to go about this.
I would like to copy a range of data to a different excel workbook that
already exists. Can anyone help me do this? For examples sake, lets say
that the range I would like to export is:

ThisWorkbook.Sheets("Sheet1").Range("A1:C3")

And lets say the address to the workbook I would like to export this range
to is:

Workbook:
C:\Destination.xls

Sheet/Location:
Test!M5

Thanks again for all of your help.

Best,
Chad

Exporting to another excel file
 

Which line causes the error?

If it's this line:

ThisWorkbook.Sheets("Sheet1").Range("A1:C3").copy _
destination:=bk.Worksheets("Test").Range("M5")

Then there is no worksheet named "Sheet1" in the workbook holding the code or
there is no worksheet named "Test" in the Destination.xls workbook.


winnie123 wrote:

I have tried this code but I get an error message

Run-time error '9'
Subscript out of range

Can you advise please.

Thank you

"Tom Ogilvy" wrote:

Dim bk as workbook
Dim bSave as Boolean

' test to see if Destination.xls is already open

On error resume next
set bk = Workbooks("Destination.xls")
On error goto 0
if bk is nothing then
bSave = True
set bk = workbooks.open("C:\Destination.xls"
End if
ThisWorkbook.Sheets("Sheet1").Range("A1:C3").copy _
destination:=bk.Worksheets("Test").Range("M5")

' if destination was originally closed, then save and close it

if bSave then bk.Close Savechanges:=True

--
regards,
Tom Ogilvy


Sheet/Location:
Test!M5


"Chad" wrote:

This may seem like a simple question but I am not sure how to go about this.
I would like to copy a range of data to a different excel workbook that
already exists. Can anyone help me do this? For examples sake, lets say
that the range I would like to export is:

ThisWorkbook.Sheets("Sheet1").Range("A1:C3")

And lets say the address to the workbook I would like to export this range
to is:

Workbook:
C:\Destination.xls

Sheet/Location:
Test!M5

Thanks again for all of your help.

Best,
Chad

--

Dave Peterson

Exporting to another excel file
 

Sorry I forgot to say where.

Its on the line

ThisWorkbook.Sheets("Sheet1").Range("A1:C3").copy _

Thank you

"winnie123" wrote:

I have tried this code but I get an error message

Run-time error '9'
Subscript out of range

Can you advise please.

Thank you

"Tom Ogilvy" wrote:

Dim bk as workbook
Dim bSave as Boolean

' test to see if Destination.xls is already open

On error resume next
set bk = Workbooks("Destination.xls")
On error goto 0
if bk is nothing then
bSave = True
set bk = workbooks.open("C:\Destination.xls"
End if
ThisWorkbook.Sheets("Sheet1").Range("A1:C3").copy _
destination:=bk.Worksheets("Test").Range("M5")

' if destination was originally closed, then save and close it

if bSave then bk.Close Savechanges:=True

--
regards,
Tom Ogilvy


Sheet/Location:
Test!M5


"Chad" wrote:

This may seem like a simple question but I am not sure how to go about this.
I would like to copy a range of data to a different excel workbook that
already exists. Can anyone help me do this? For examples sake, lets say
that the range I would like to export is:

ThisWorkbook.Sheets("Sheet1").Range("A1:C3")

And lets say the address to the workbook I would like to export this range
to is:

Workbook:
C:\Destination.xls

Sheet/Location:
Test!M5

Thanks again for all of your help.

Best,
Chad

Exporting to another excel file
 

Yes it was that line, I was being stupid and had the wrong file name.

Now I get another error

Runtime error 438
object doesn't support this property or method, on first row below.

ThisWorkbook.Sheets("FLEX ORDER INTAKE").Range("A1:J1000").Copy_
Destination = bk.Worksheets("openorders").Range("T1")

Do I have to define Thisworkbook?

Thanks

Full code below

Sub copytoMaster()


Dim wbkCurrent As Workbook
Dim wbkNew As Workbook

Dim bk As Workbook
Dim bSave As Boolean

' test to see if Destination.xls is already open

On Error Resume Next
Set bk = Workbooks("Master.xls")
On Error GoTo 0
If bk Is Nothing Then
bSave = True
Set bk = Workbooks.Open("C:\Flex Forecast Summary\Master.xls")
End If
ThisWorkbook.Sheets("FLEX ORDER INTAKE").Range("A1:J1000").Copy_
Destination = bk.Worksheets("openorders").Range("T1")

' if destination was originally closed, then save and close it

If bSave Then bk.Close Savechanges:=True

End Sub

"Dave Peterson" wrote:

Which line causes the error?

If it's this line:

ThisWorkbook.Sheets("Sheet1").Range("A1:C3").copy _
destination:=bk.Worksheets("Test").Range("M5")

Then there is no worksheet named "Sheet1" in the workbook holding the code or
there is no worksheet named "Test" in the Destination.xls workbook.


winnie123 wrote:

I have tried this code but I get an error message

Run-time error '9'
Subscript out of range

Can you advise please.

Thank you

"Tom Ogilvy" wrote:

Dim bk as workbook
Dim bSave as Boolean

' test to see if Destination.xls is already open

On error resume next
set bk = Workbooks("Destination.xls")
On error goto 0
if bk is nothing then
bSave = True
set bk = workbooks.open("C:\Destination.xls"
End if
ThisWorkbook.Sheets("Sheet1").Range("A1:C3").copy _
destination:=bk.Worksheets("Test").Range("M5")

' if destination was originally closed, then save and close it

if bSave then bk.Close Savechanges:=True

--
regards,
Tom Ogilvy


Sheet/Location:
Test!M5


"Chad" wrote:

This may seem like a simple question but I am not sure how to go about this.
I would like to copy a range of data to a different excel workbook that
already exists. Can anyone help me do this? For examples sake, lets say
that the range I would like to export is:

ThisWorkbook.Sheets("Sheet1").Range("A1:C3")

And lets say the address to the workbook I would like to export this range
to is:

Workbook:
C:\Destination.xls

Sheet/Location:
Test!M5

Thanks again for all of your help.

Best,
Chad

--

Dave Peterson

Exporting to another excel file
 

ThisWorkbook.Sheets("FLEX ORDER INTAKE").Range("A1:J1000").Copy _
Destination:=bk.Worksheets("openorders").Range("T1 ")

The space-underscore is the continuation character.

And the continuation line is passing a value to the destination argument, so you
need the ":=" assignment symbol.

winnie123 wrote:

Yes it was that line, I was being stupid and had the wrong file name.

Now I get another error

Runtime error 438
object doesn't support this property or method, on first row below.

ThisWorkbook.Sheets("FLEX ORDER INTAKE").Range("A1:J1000").Copy_
Destination = bk.Worksheets("openorders").Range("T1")

Do I have to define Thisworkbook?

Thanks

Full code below

Sub copytoMaster()

Dim wbkCurrent As Workbook
Dim wbkNew As Workbook

Dim bk As Workbook
Dim bSave As Boolean

' test to see if Destination.xls is already open

On Error Resume Next
Set bk = Workbooks("Master.xls")
On Error GoTo 0
If bk Is Nothing Then
bSave = True
Set bk = Workbooks.Open("C:\Flex Forecast Summary\Master.xls")
End If
ThisWorkbook.Sheets("FLEX ORDER INTAKE").Range("A1:J1000").Copy_
Destination = bk.Worksheets("openorders").Range("T1")

' if destination was originally closed, then save and close it

If bSave Then bk.Close Savechanges:=True

End Sub

"Dave Peterson" wrote:

Which line causes the error?

If it's this line:

ThisWorkbook.Sheets("Sheet1").Range("A1:C3").copy _
destination:=bk.Worksheets("Test").Range("M5")

Then there is no worksheet named "Sheet1" in the workbook holding the code or
there is no worksheet named "Test" in the Destination.xls workbook.


winnie123 wrote:

I have tried this code but I get an error message

Run-time error '9'
Subscript out of range

Can you advise please.

Thank you

"Tom Ogilvy" wrote:

Dim bk as workbook
Dim bSave as Boolean

' test to see if Destination.xls is already open

On error resume next
set bk = Workbooks("Destination.xls")
On error goto 0
if bk is nothing then
bSave = True
set bk = workbooks.open("C:\Destination.xls"
End if
ThisWorkbook.Sheets("Sheet1").Range("A1:C3").copy _
destination:=bk.Worksheets("Test").Range("M5")

' if destination was originally closed, then save and close it

if bSave then bk.Close Savechanges:=True

--
regards,
Tom Ogilvy


Sheet/Location:
Test!M5


"Chad" wrote:

This may seem like a simple question but I am not sure how to go about this.
I would like to copy a range of data to a different excel workbook that
already exists. Can anyone help me do this? For examples sake, lets say
that the range I would like to export is:

ThisWorkbook.Sheets("Sheet1").Range("A1:C3")

And lets say the address to the workbook I would like to export this range
to is:

Workbook:
C:\Destination.xls

Sheet/Location:
Test!M5

Thanks again for all of your help.

Best,
Chad

--

Dave Peterson


--

Dave Peterson

Exporting to another excel file
 

Ps.

No. ThisWorkbook is always the workbook that owns the code that's running.

winnie123 wrote:

Yes it was that line, I was being stupid and had the wrong file name.

Now I get another error

Runtime error 438
object doesn't support this property or method, on first row below.

ThisWorkbook.Sheets("FLEX ORDER INTAKE").Range("A1:J1000").Copy_
Destination = bk.Worksheets("openorders").Range("T1")

Do I have to define Thisworkbook?

Thanks

Full code below

Sub copytoMaster()

Dim wbkCurrent As Workbook
Dim wbkNew As Workbook

Dim bk As Workbook
Dim bSave As Boolean

' test to see if Destination.xls is already open

On Error Resume Next
Set bk = Workbooks("Master.xls")
On Error GoTo 0
If bk Is Nothing Then
bSave = True
Set bk = Workbooks.Open("C:\Flex Forecast Summary\Master.xls")
End If
ThisWorkbook.Sheets("FLEX ORDER INTAKE").Range("A1:J1000").Copy_
Destination = bk.Worksheets("openorders").Range("T1")

' if destination was originally closed, then save and close it

If bSave Then bk.Close Savechanges:=True

End Sub

"Dave Peterson" wrote:

Which line causes the error?

If it's this line:

ThisWorkbook.Sheets("Sheet1").Range("A1:C3").copy _
destination:=bk.Worksheets("Test").Range("M5")

Then there is no worksheet named "Sheet1" in the workbook holding the code or
there is no worksheet named "Test" in the Destination.xls workbook.


winnie123 wrote:

I have tried this code but I get an error message

Run-time error '9'
Subscript out of range

Can you advise please.

Thank you

"Tom Ogilvy" wrote:

Dim bk as workbook
Dim bSave as Boolean

' test to see if Destination.xls is already open

On error resume next
set bk = Workbooks("Destination.xls")
On error goto 0
if bk is nothing then
bSave = True
set bk = workbooks.open("C:\Destination.xls"
End if
ThisWorkbook.Sheets("Sheet1").Range("A1:C3").copy _
destination:=bk.Worksheets("Test").Range("M5")

' if destination was originally closed, then save and close it

if bSave then bk.Close Savechanges:=True

--
regards,
Tom Ogilvy


Sheet/Location:
Test!M5


"Chad" wrote:

This may seem like a simple question but I am not sure how to go about this.
I would like to copy a range of data to a different excel workbook that
already exists. Can anyone help me do this? For examples sake, lets say
that the range I would like to export is:

ThisWorkbook.Sheets("Sheet1").Range("A1:C3")

And lets say the address to the workbook I would like to export this range
to is:

Workbook:
C:\Destination.xls

Sheet/Location:
Test!M5

Thanks again for all of your help.

Best,
Chad

--

Dave Peterson


--

Dave Peterson

Exporting to another excel file
 

Thanks very much Dave,

It works at last, yipee :-)


"Dave Peterson" wrote:

ThisWorkbook.Sheets("FLEX ORDER INTAKE").Range("A1:J1000").Copy _
Destination:=bk.Worksheets("openorders").Range("T1 ")

The space-underscore is the continuation character.

And the continuation line is passing a value to the destination argument, so you
need the ":=" assignment symbol.

winnie123 wrote:

Yes it was that line, I was being stupid and had the wrong file name.

Now I get another error

Runtime error 438
object doesn't support this property or method, on first row below.

ThisWorkbook.Sheets("FLEX ORDER INTAKE").Range("A1:J1000").Copy_
Destination = bk.Worksheets("openorders").Range("T1")

Do I have to define Thisworkbook?

Thanks

Full code below

Sub copytoMaster()

Dim wbkCurrent As Workbook
Dim wbkNew As Workbook

Dim bk As Workbook
Dim bSave As Boolean

' test to see if Destination.xls is already open

On Error Resume Next
Set bk = Workbooks("Master.xls")
On Error GoTo 0
If bk Is Nothing Then
bSave = True
Set bk = Workbooks.Open("C:\Flex Forecast Summary\Master.xls")
End If
ThisWorkbook.Sheets("FLEX ORDER INTAKE").Range("A1:J1000").Copy_
Destination = bk.Worksheets("openorders").Range("T1")

' if destination was originally closed, then save and close it

If bSave Then bk.Close Savechanges:=True

End Sub

"Dave Peterson" wrote:

Which line causes the error?

If it's this line:

ThisWorkbook.Sheets("Sheet1").Range("A1:C3").copy _
destination:=bk.Worksheets("Test").Range("M5")

Then there is no worksheet named "Sheet1" in the workbook holding the code or
there is no worksheet named "Test" in the Destination.xls workbook.


winnie123 wrote:

I have tried this code but I get an error message

Run-time error '9'
Subscript out of range

Can you advise please.

Thank you

"Tom Ogilvy" wrote:

Dim bk as workbook
Dim bSave as Boolean

' test to see if Destination.xls is already open

On error resume next
set bk = Workbooks("Destination.xls")
On error goto 0
if bk is nothing then
bSave = True
set bk = workbooks.open("C:\Destination.xls"
End if
ThisWorkbook.Sheets("Sheet1").Range("A1:C3").copy _
destination:=bk.Worksheets("Test").Range("M5")

' if destination was originally closed, then save and close it

if bSave then bk.Close Savechanges:=True

--
regards,
Tom Ogilvy


Sheet/Location:
Test!M5


"Chad" wrote:

This may seem like a simple question but I am not sure how to go about this.
I would like to copy a range of data to a different excel workbook that
already exists. Can anyone help me do this? For examples sake, lets say
that the range I would like to export is:

ThisWorkbook.Sheets("Sheet1").Range("A1:C3")

And lets say the address to the workbook I would like to export this range
to is:

Workbook:
C:\Destination.xls

Sheet/Location:
Test!M5

Thanks again for all of your help.

Best,
Chad

--

Dave Peterson


--

Dave Peterson