After copying in Macro - come back to previous sheet

I want to copy info from one sheet to another, come back and copy info to another sheet. I want to do this from alot of sheets. My goal is to remember the first page as a variable so if I do this on other sheets it will remember the sheet to come back to without using the tab name.

example of what I want

HTML Code: 

then copy again to another sheet...but I do not want to use the name because if the macro is on a different sheet than I need that sheet name which would be way too much to do

HTML Code: 

There is no need to select the range and then copy the range. You can specify the range to be copied in one step. (the blue line below)

The following will copy the A1:Z100 range and then paste that information into sheets 2 and 3 in the same range of cells.

Please Login or Register  to view this content.

Originally Posted by JK1234

This should work but my source page may change from sheet1 to another sheet and I was hoping to use this same macro on each sheet. So then sheet1 would be the active sheet. Then after it copied into the other two sheets then come back to this same active sheet.
So depending upon what sheet it is on would depend what is in the macro.

But I like the new expression, going to start using that instead.

Maybe something like this change to mdbct's code

Please Login or Register  to view this content.

If I understand correctly from this:

This should work but my source page may change from sheet1 to another sheet and I was hoping to use this same macro on each sheet. So then sheet1 would be the active sheet. Then after it copied into the other two sheets then come back to this same active sheet. So depending upon what sheet it is on would depend what is in the macro.

... you're saying that you want the same code to run for different source sheets? You seem to be thinking that as you copy to other sheets those become active, but with the code mcbct provided, this is not the case and the source remains the active sheet. So - if this understanding is right, you want it to run where the source is the activesheet ... try this:

Please Login or Register  to view this content.

Or you can pass the name into the routine as a parameter based on however you determine the source; e.g. call like this:

Please Login or Register  to view this content.

Hope that helps.

You can get the required raange by recording a macro

Please Login or Register  to view this content.

Hi - I've changed the code below just to show you some options about this sort of thing; I do recommend though that you have a think about how your ranges are defined (static or dynamic) and you might be able to create a loop that does the same thing. I've made this as generic as I can to demonstrate and you can choose the most appropriate option for what you're doing ...

Please Login or Register  to view this content.

Hope that helps ... MM.

I'm not sure why you are doing this, can you explain? There might be a simpler way to do it. You could use Named Ranges for example.

I would start by using Data validation instead of the comboboxes

Also get rid of the merged cells, they should be avoided because they will cause problems sooner or later.

Instead of multiple Ifs, use Select Case, it's easier to follow.

Here's how your case should look, but I don't understand what you mean by "for each selection" ... each selection of what?

Please Login or Register  to view this content.

Not sure if this is what you want, sheet references might need changing, but I have attempted to change your code to use Select Case

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Versus:

Please Login or Register  to view this content.

The 2nd one should be

Please Login or Register  to view this content.

... so it goes to the right sheet, otherwise you're cutting & pasting to the same place in the active sheet ..

Or - since you've got different variables for the same range address (as far as I can tell from the snippet) then set Rng2 explicitly such as:

Please Login or Register  to view this content.

And then you can:

Please Login or Register  to view this content.

Hope that helps

Ah ha! I think it may be because Rng2 is only set if a particular case condition is met ... When stepping through, hover over Rng2 and if it says "Nothing", then that's your problem

I think you may have missed what Phil_V and I were saying ... It is possible that if your conditions are not met in the case statements, that neither Ws or Rng1 or Rng2 are ever set (so their value would be 'Nothing'). If this is right, then the attempt to copy/paste at the end (which is not conditional) is hit and your objects are not set ... Make sense? Think about running through this where N5 = 6 ...

... Actually ... that's not it ... I simplified the code to test as:

Please Login or Register  to view this content.

But it doesn't like that either, which I didn't expect ... sorry

Rng doesn't need anything in front of it, it's already completely parented: It refers to a range on a worksheet in a workbook.

Sorry, this thread's gone on way too long to read all of it.

I'm happy to help if it can be explained clearly in a nutshell.

You need to think through how you are setting your values

Please Login or Register  to view this content.

This code was only based on what you originally posted & was to demonstrate how the Select Case is set up.

I would think you first need to determine a value for the worksheet to paste to Then the range on that sheet to copy to.

I have simplified this, it shoulod give you an idea what you need to do.