About Us

winnie123

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
exporting from an excel file to outlook	H	Excel Discussion (Misc queries)	2	April 20th 08 05:17 PM
Exporting excel to text file	Exceluser	Excel Discussion (Misc queries)	1	July 1st 05 03:04 AM
Exporting excel to text file	Exceluser	Excel Discussion (Misc queries)	1	June 29th 05 05:26 AM
Excel VBA - Exporting values to a txt file	schumacker	Excel Programming	2	February 24th 04 09:18 AM

Menu

About Us