Sheetname as a variable

[jomili]

If I have this line in my macro triggered to run when I click in a specific cell :

Please Login or Register  to view this content.

and in that cell I clicked I have the name of the worksheet I want to copy to (instead of "Sheet2", maybe "DestSheet"), how do I change Sheet2 to DestSheet?

[Richard Buttrey]

Hi,

If I've understood correctly try:

Please Login or Register  to view this content.

HTH

[jomili]

Richard,

I tried your code, as shown below, but it didn't work. It gives me a subscript out of range error.

Please Login or Register  to view this content.

So then I tried adapting come code I was using in another workbook. The original code copies the activecell value to another sheet. I tried to adapt it to copy the range of the row I want to copy and paste (columns A through F of the row I make the change in), but that also didn't work:

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

Can you upload your workbook, anonymised if necessary?

Rgds

[jomili]

You bet.

Essentially what I want to do at this step is: whenever I add a line into Masterlist, and click the dropdown for whatever region it belongs to, the macro will pick up that line and paste it into the appropriate worksheet. Later on I'll put more routines in to calculate the time remaining and send out emails of the individual worksheets to the responsible parties.

The reports on the Masterlist will change as new reports get added, and as some become no longer necessary (is there a way to delete the corresponding line on the appropriate worksheet if I delete it in the Masterlist? Something else to think about...).

I appreciate your help getting me started. I'm still a neophyte at this VBA stuff, so get lost pretty easily.

[Richard Buttrey]

Hi,

Try the following procedure

Please Login or Register  to view this content.

This will update the relevant sheet when you make a change in column I.

Note however that it will add the MasterList row whenever you change column I cells, so you'll probably want to build in an additional check that the row doesn't already exist in the destination sheet, but hopefully this will get you started.

Rgds

[jomili]

Richard, You Rock! It works beautifully.

To make a check, I'd need to compare the value in A of the line I just pasted with the values of A in all other lines, and if they match delete the older line (or maybe I'll just do a message box...hmmm, I'll have to think about that one). How would I refer to the A cell in the line I just pasted? Would it be

Please Login or Register  to view this content.

or something like that?

Also, would it be possible, instead of pasting the line, to paste only the link? In that way, if I delete a report off the Masterlist, it would also delete from the related worksheet, right?

[Richard Buttrey]

Hi,

The following will check for existing values and create the relevant links if not.

Please Login or Register  to view this content.

Rgds

[Richard Buttrey]

Hi,

Just realised that deleting from the Master List will give you #REFs on the other sheets. You'll need to actually delete the relevant row from the linking sheet. I'm just going out at the moment so can't modify it yet.

However hopefully you should be able to use the Find logic and delete the relevant row from the linking sheet. If you have problems just post back and I'll pick it up when I'm next on line.

Regards

[jomili]

Richard,

The pasting worked just fine, but the checking for duplicate didn't seem to do anything. To test, on the masterlist I copied one line a second time (which triggered the macro to deliver an error message; I cleared it and went on). I changed both lines, one at a time, to Region 1, and both lines were pasted on the Region 1 tab. I tried it with each region and got identical results. I think it's because it pastes first, then checks to see if it's a duplicate, and if so exits the sub without deleting what it just pasted.

I see how deleting the line on Master DOES give me #Refs. I've got other assignments I've got to do, but will look into using FIND to get rid of the #Refs lines.