November 7th, 2012, 03:32 PM
VBA Tab referencing - tricky
So I've researched online for hours, and gone through some of my reference books. I can't seem to find an answer to this specific issue with referencing the worksheet objects.
I'm trying to create a name in my macro that is 1/2 fixed and 1/2 variable, with the variable being based on a cell value. It seems possible, I'm sure there is just a simple error in my syntax that I can't figure out. Here is what I'm trying to do:
Range C4 = dropdown list with first names
Tabs = There is a tab with just the name, (i.e. "Bob"), and another tab that is the name and "-Goals". So Bob would have two tabs, "Bob" and "Bob-Goals". The tabs and their names are created and fixed. So I am trying to do a simple copy and paste of the Range C9:C19, but I want the values to paste into the "-Goals" sheet for whichever name is in C4, instead of creating a separate IF statement for every single tab. Here is what I have now that isn't working currently....
Dim RMName As String
RMName = Range("C4")
Worksheets(RMName - Goals).Range ("C5")
I have tried every combination I can think of for the last line above, using "" and the & connectors. As it shows above, the bug message I get is "Type mismatch". Any help?