Return to active sheet-cell content copy

[nigelog]

Try as I might I cannot get two adjacents sheets in workbook to switch between each other. In the attached example "macro print daf fax" copies a shape from the header sheet to whatever sheet is active, prior to printing and deleting shape. (to ensure workbook only contains one copy of logo). The last part of the macro toolbar I am trying to create "macro print garage worksheet" has to be able to copy selected cells to sheet TRUCKS, print out and then delete entries to leave TRUCKS blank for next use. What I cannot do is get passed the first of several cell copies. I have a blank in understanding what I am asking VB to do.
Basically I want to copy
B22 from active sheet to TRUCKS D3
I22 from initial sheet to TRUCKS L4
I23 from initial sheet to TRUCKS L3
E23 from initial sheet to TRUCKS J3
C28:30 from initial sheet to TRUCKS B6:8
C31:33 from initial sheet to TRUCKS H6:8
Print the TRUCKS sheet
Clear the transferred contents and return to Initial sheet.

I do not expect anyone to do this work for me but would appreciate if someone could point me in the right direction for copying from a sheet from which the macro is invoked to a target sheet. Many thanks Nigel

[Rick_Stanich]

What is "active sheet" and "initial sheet"?

[nigelog]

The Active sheet is the sheet opened when then macro on the menu bar is invoked. This is the sheet (initial sheet) that I wish to return to. I cannot reference Sheet "name" as the macro will be invoked from numerous sheets all named differently. Hope this is clearer. Rgds Nigel

[royUK]

There's no need to activate any sheets to copy data. There's lots of examples on the Forum, I've posted several, that demonstrate this.

In fact the code that I gave you in your earlier post did not select any sheets

http://www.excelforum.com/showthread.php?t=643324

[nigelog]

I appreciate what you have stated and tried several variations of the code you kindly forwarded. What was happening though, the first data transfer was correct but the subsequent data transfer requests continued within the target sheet rather than reverting to the source sheet to pick up the next item. Can I list all the data requests (ranges) in one block within the code, for transfer to the target sheet or do they have to be executed one after another. Forgive my inability to get over this stumbling block.

Hard lessons are seldom forgotten and I am finding this one hard.

Rgds Nigel

[royUK]

You need all the code within the With ws section. Try this

Please Login or Register  to view this content.

[nigelog]

Hi Roy,
I must be compicating the issue. The code you have returned was the fax header macro which was completed using the code you posted last week. The info request this week was for the a worksheet to be printed. I will use what you have forwarded to see if it can be adapted for this use. Thanks for the help. Rgds Nigel

[nigelog]

Hi (Roy if available)

I finally have a working code for printing a worksheet. I worked backwards by activating the TRUCKS worksheet and then requesting the info from the Ws.Long winded but it was the only way I could step through it myself. My problem is with the last action. I want to finish on the initial sheet from where the print worksheet was activated, but, every time I added a new "With Ws" I could not find a command that would just return to the sheet.

Can anyone help with a last line to do this - copy of code below and sample worksheet attached.

Please Login or Register  to view this content.

Many thanks

[nigelog]

Found that if I added all was OK thanks for all help. Rgds nigel

Please Login or Register  to view this content.