assign sheet to variable by code name in another workbook

I have been unable to get this to work. It errors out telling me I have not defined the variable, which is actually the sheet code name. The workbook sets fine. It's the sheet's code name.

I have tried:

Please Login or Register  to view this content.

and I've tried:

Please Login or Register  to view this content.

I appreciate your time and help.

Thanks

Hi karen53,

Try one of the following (both tested and working). You will get a runtime error if a Workbook Name or Worksheet name is spelled incorrectly and/or doesn't exist:

Please Login or Register  to view this content.

There are several additional ways to do the same thing.

To prevent typos from ruining days and weeks of work 'Option Explicit' is NEEDED at the top of each code module. This prevents errors caused by missspellings and FORCES every variable to be DECLARED (e.g. dim i as Integer). http://www.cpearson.com/excel/DeclaringVariables.aspx

Lewis

HI LJMetzger,

Thank you for your response.

Yes, I have Option Explicit at the top of all of my modules.

Setting the workbooks is not a problem. The workbooks set fine.

Set FromWs = fromwb.activesheet will not work either as it is not the activesheet, nor is it even in the current workbook.

I have tried activating the workbook before I set the worksheet but I still get the same error.

How do I address the sheet code name located in a different workbook to set the worksheet in the current workbook?

Thanks,

It' good that you're using 'Option Explicit'.

Try single stepping through the following Macro and observing how 'Book2.xls' changes.

Please Login or Register  to view this content.

If this doesn't point you in the correct direction, you're going to give us some details about what you are trying to do. For example, putting a copy of Sheet1 in the Source Workbook in the Destination Workbook, or copying values only, or copying formulas only, or copying formats only.

Lewis

Thank you Trebor! I was going crazy!