PDA

View Full Version : Usng workbook/worksheet variable names



YossiD
06-01-2017, 04:05 AM
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
06-01-2017, 05:47 AM
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
06-01-2017, 08:49 AM
Just right - so simple when you know the right syntax!

Many thanks.

Yossi

GTO
06-05-2017, 11:51 PM
You are most welcome :)