Hi, I'm using 2 workbooks in my forecast model. Can some help with a macro
to transfer data between the workbooks?

I have so far recorded a copy-paste special macro to transfer data, but what
I want to do is:
- If the workbook is open then activate the relevant sheet and copy and
paste data between workbooks, otherwise open the relevant workbook.
- Be able to store the 2 workbooks in any directory and still have the macro
work.

I've tried the following macro - but I get an error with the use of the Me.
construct:

Sub CopyData()

Dim sFileName As String
sFileName = "SpreadsheetB.xls"

On Error Resume Next
Workbooks("SpreadsheetB.xls").Activate
If Err.Number 0 Then
Workbooks.Open Me.Path & "\" & sFileName
End If
On Error GoTo 0

Sheets("Forecast").Select
Rows("2:270").Select
Selection.Copy
......

Does anyone tell me what I've done wrong here?

Thanks

Elijah

Me refers to a container, such as a form or a worksheet class. You probably
want ActiveWorkbook here, or ThisWorkbook if it will be the workbook that
the macro is stored in.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Elijah" wrote in message
...
Hi, I'm using 2 workbooks in my forecast model. Can some help with a macro
to transfer data between the workbooks?

I have so far recorded a copy-paste special macro to transfer data, but
what
I want to do is:
- If the workbook is open then activate the relevant sheet and copy and
paste data between workbooks, otherwise open the relevant workbook.
- Be able to store the 2 workbooks in any directory and still have the
macro
work.

I've tried the following macro - but I get an error with the use of the
Me.
construct:

Sub CopyData()

Dim sFileName As String
sFileName = "SpreadsheetB.xls"

On Error Resume Next
Workbooks("SpreadsheetB.xls").Activate
If Err.Number 0 Then
Workbooks.Open Me.Path & "\" & sFileName
End If
On Error GoTo 0

Sheets("Forecast").Select
Rows("2:270").Select
Selection.Copy
.....

Does anyone tell me what I've done wrong here?

Thanks

Elijah

use thisworkbook.path

--
Don Guillett
SalesAid Software

"Elijah" wrote in message
...
Hi, I'm using 2 workbooks in my forecast model. Can some help with a macro
to transfer data between the workbooks?

I have so far recorded a copy-paste special macro to transfer data, but
what
I want to do is:
- If the workbook is open then activate the relevant sheet and copy and
paste data between workbooks, otherwise open the relevant workbook.
- Be able to store the 2 workbooks in any directory and still have the
macro
work.

I've tried the following macro - but I get an error with the use of the
Me.
construct:

Sub CopyData()

Dim sFileName As String
sFileName = "SpreadsheetB.xls"

On Error Resume Next
Workbooks("SpreadsheetB.xls").Activate
If Err.Number 0 Then
Workbooks.Open Me.Path & "\" & sFileName
End If
On Error GoTo 0

Sheets("Forecast").Select
Rows("2:270").Select
Selection.Copy
.....

Does anyone tell me what I've done wrong here?

Thanks

Elijah

Thanks Don, Bob

much appreciated.

Elijah
"Don Guillett" wrote in message
...
use thisworkbook.path

--
Don Guillett
SalesAid Software

"Elijah" wrote in message
...
Hi, I'm using 2 workbooks in my forecast model. Can some help with a
macro
to transfer data between the workbooks?

I have so far recorded a copy-paste special macro to transfer data, but
what
I want to do is:
- If the workbook is open then activate the relevant sheet and copy and
paste data between workbooks, otherwise open the relevant workbook.
- Be able to store the 2 workbooks in any directory and still have the
macro
work.

I've tried the following macro - but I get an error with the use of the
Me.
construct:

Sub CopyData()

Dim sFileName As String
sFileName = "SpreadsheetB.xls"

On Error Resume Next
Workbooks("SpreadsheetB.xls").Activate
If Err.Number 0 Then
Workbooks.Open Me.Path & "\" & sFileName
End If
On Error GoTo 0

Sheets("Forecast").Select
Rows("2:270").Select
Selection.Copy
.....

Does anyone tell me what I've done wrong here?

Thanks

Elijah