Macro Help

[HunterS93]

Hi, I am trying to combine 3 interactive maps that I have made, into one workbook. They are all on there own separate workbook but I want to combine them all so I can look through all of them on the same workbook. I keep having the problem when I try to combine the workbooks because the names I used to label the corresponding info that is used in the Macro are the same. So when I combine them the macro keeps giving me an error. I don't completely understand how all of the fields correspond to the macro but I am looking for a solution to get them all to work on the same page by maybe changing how I name them.

Here is the Macro:
Sub ColorCode

For i = 3 To 53
Range("actReg").Value = Range("Macro!A" & i).Value
ActiveSheet.Shapes(Range("actReg").Value).Select
Selection.ShapeRange.Fill.ForeColor.RGB = Range(Range("actRegCode").Value).Interior.Color

Next i


Range("U21").Select
End Sub

The names that are the same are actReg, regData, actRegValue, actRegCode, class0, class1, class2, class3, class4, and class5.
I was wondering if I could change those names so that all three macros would run properly and what I would have to change in the macro to make it run properly.

 

[JLGWhiz]

Having the same range names is not really a problem. The problem that I see is that you are not qualifying your source range nor your destination range with the parent information like workbook and sheet. I would suggest that at the beginning of the macro, you declare some variables for your workbooks and sheets so that you know which you are referring to later in the code. Example:

Dim wb1 As Workbook, wb2 As Workbook, wb3 As Workbook, sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
Set wb1 = ThisWorkbook
Set wb2 = Workbooks("Maps2") 'sub real name
Set wb2 = Workbooks("Maps3") 'sub real name
Set sh1 = wb1.Sheets(1) 'Use actual sheet name enclosed in quotes v. index
Set sh2 = wb2.Sheets(1) 'Use actual sheet name enclosed in quotes v. index
Set sh3 = wb3.Sheets(1) 'Use actual sheet name enclosed in quotes v. index

Then when you refer to your range you would use a qualifier sh1.Range("actReg") or sh2.Range("actReg") and Excel wull know which workbook and which sheet you are referring to. In this case, sh1, sh2 and sh3 are inclusive of their parent workbook so there is no need to use wb1.sh1, just use sh1 and Excel knows the workbook.

 

[HunterS93]

Thanks for the help but I am still having trouble with them having the same name. If they are all combined into the same workbook when I set the variables would I still need to label them as 3 different workbooks? or should I just set variables for each sheet that the macro should apply to so that it knows what range I am referring to?

 

[JLGWhiz]

Thanks for the help but I am still having trouble with them having the same name. If they are all combined into the same workbook when I set the variables would I still need to label them as 3 different workbooks? or should I just set variables for each sheet that the macro should apply to so that it knows what range I am referring to?

The method you are using to put the data into the consolidated workbook should not be affected by the name of the source range, since you are only using the value of the cell content and not the attributes of the range. Put simply, you only need to qualify the source range to get the data so that Excel and VBA know where to look for it. Once it has been put into the consolidated sheet, the source identity is lost.