Solved: Writing to specific sheets on specific cells

[dhartsfield]

I am working with Microsoft Visual Basic 6.3 for Excel. I am trying figure out if it is possible to take information that is being called upon from another workbook and place it in to another workbook to specific cells in a specific worksheet. I am an ultra newbie to VBA and I have no clue what to do. Everything I try based on help files I have viewed just results in posting to the active sheet, not the specified sheet. Is this possible or am I just barking up an invisible tree? If there is anyone out there with the brilliant mind to help me, I would be in your debt. Thank You!

[Bob Phillips]

[vba]
Workbooks("myBook.xls").Worksheets("Sheet1").Range("A1:A20").Copy _
Workbooks("my other book.xls").Worksheets("Sheet1").Range("A1")
[/vba]

is a starting point. All names can be changed to appropriate values.

[RonMcK]

dhartsfield,

Xid gave you the very elegant solution.

Since you're new to VBA, what you may find it helpful to see the code that Excel's macro recorder creates to do what you want done.

Fire up Excel, turn on macro recorder, open your source and your target workbooks (WBs), select the sheet and range of cells to copy from in the source WB, click Edit > Copy (or ^C (or Cmd-C on Mac)), then, select the target WB and on it the desired WS, click on the first cell, click Edit > Paste (or ^V (or Cmd-V on Mac)). Then, shut off the recorder.

Open VBE (alt-F11 on PC or Tools > Macro > VBE), find and open the module where Excel recorded your macro. Read it to see how how Excel coded the work you asked it to do.

With that in hand (or rather on screen) compare the code Excel wrote with the line of code Xid gave you. I believe this will illuminate what is happening 'under the hood' in Xid's solution. And, you'll see how his solution saves you a lot of coding; and, I venture that it will run much quicker than all that code.

Cheers!

[dhartsfield]

Thanks for your help! It is a big help to me!