Locate worksheet according to specific value

Hi All,

Could anyone help me out with this? Here is the VBA:

Please Login or Register  to view this content.

Analysis.xls is workbook B and I need to copy the data from workbook A to workbook B's specific worksheet as indicated in cell H6. I have already pre-defined a few worksheets and H6 will show the worksheet name upon data importing. But, I couldn't get excel to copied the data to the specify worksheet and it has to be dynamic as mention.

Hope you guys can help.

Thank you

Justin

Two things I'd suggest:

1. Step through your macro in the VB Editor line-by-line to see where things fall apart. Click your mouse anywhere inside the macro code and press F8 to move line-by-line. You'll be able to see things happen and to hover over variable names as they change value.

2. You're using multiple workbooks, worksheets and ranges. When doing so, it is always best to fully qualify each object (whether it be range, defined name, etc).

For example:

Please Login or Register  to view this content.

Wherever you use Range, you should also be specifying the workbook and worksheet rather than relying on Activate and Select events (which are unnecessary to begin with 99% of the time).

Please Login or Register  to view this content.

To save some typing you could always use the With/EndWith construct. Just be careful when nesting With statements to make sure you're referring to the proper object.

Please Login or Register  to view this content.