Usng workbook/worksheet variable names

[YossiD]

Have done some searching but could not find the answer to this undoubtedly simple question.

I am trying to write a macro (Excel 2003) to copy data from one workbook to another, based on some search results.

Since the workbook and worksheet names are long, I am assigning variable names to them for convenience and readability of the code.

Here's what I've done, but doesn't work:

Dim L1 As Workbook
Dim LS1 As Worksheet
Dim L2 As Workbook
Dim LS2 As Worksheet
Set L1 = Workbooks ("Long Filename 1")
Set LS1 = Worksheet ("Long Sheetname 1")
Set L2 = Workbooks ("Long Filename 2")
Set LS2 = Worksheet ("Long Sheetname 2")

<Search routine here>

'This is the statement I want to use to copy from one place to the other

L1.LS1.Cells(<target address>)=L2.LS2.Cells(<source address>)

Both workbooks are open when I try to run this.

What am I doing wrong?

TIA

[GTO]

Hi Yossi,

L1, LS1 etc are a bit unusual, let us use WB and WS (WorkBook and WorkSheet).

Dim WB1 As Workbook
Dim WB2 As Workbook
Dim WS1 As Worksheet
Dim WS2 As Worksheet


Set WB1 = Workbooks("Some Long Filename")
Set WB2 = Workbooks("Some other Filename")


Set WS1 = WB1.Worksheets("A Sheet Name")
Set WS2 = WB2.Worksheets("Another sheet name")


WS1.Range("A1").Value = WS2.Range("A1").Value

As you see, when we set the worksheets, we reference which workbook the sheet is in by qualifying (the WB1.Worksheets part).

Does that help?

Mark

[YossiD]

Just right - so simple when you know the right syntax!

Many thanks.

Yossi

[GTO]

You are most welcome