• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

Excel Macro 2 x Dim declarations?

Hello,

I'm trying to define two names via the dim function. These names are being defined for two different non-active sheets.

When I run the macro below I get the message:
Duplicate declaration in current scope

- with the error highlighting row 11.

What's the best way of defining these names for non active sheets?

Sub mac_quickformulas()
'
' mac_quickformulas Macro
'
Dim uniqueIDformulas As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Unique ID")
uniqueIDformulas = ws.Range("B" & Rows.Count).End(xlUp).Row

Dim dataformulas As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Data")
dataformulas = ws.Range("B" & Rows.Count).End(xlUp).Row

'Add forumula columns to the Unique ID Sheet
    Sheets("Unique ID").Select
    Range("L2").Select
    ActiveCell.FormulaR1C1 = _
        "=COUNTIFS(Data!C[-11],"">0"",Data!C[-6],'Unique ID'!RC[-5],Data!C[-9],"">0"")"
    Range("M2").Select
    ActiveCell.FormulaR1C1 = _
        "=COUNTIFS(Data!C[-12],"">0"",Data!C[-7],'Unique ID'!RC[-6],Data!C[-10],"""")"
    Range("N2").Select
    ActiveCell.FormulaR1C1 = _
        "=(INDEX('Australia Post Codes'!C[-10],MATCH('Unique ID'!RC[-5],'Australia Post Codes'!C[-12],0)))"
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "Region"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "Jobs"
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "Quotes"
    Range("M2").Select
    Range("L2:N2").AutoFill Destination:=Range("L2:N" & uniqueIDformulas)

'Add formula columns to the Data Sheet
    Sheets("Data").Select
    Range("K1").Select
    ActiveWindow.SmallScroll Down:=-6
    ActiveCell.FormulaR1C1 = "Region"
    Range("K2").Select
    ActiveCell.FormulaR1C1 = _
        "=(INDEX('Australia Post Codes'!C[-7],MATCH(RC[-3],'Australia Post Codes'!C[-9],0)))"
    Range("L2").Select
    ActiveCell.FormulaR1C1 = _
        "=(INDEX('Unique ID'!C[-11],MATCH(Data!RC[-6],'Unique ID'!C[-5],0)))"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "ID"
    Range("L2").Select
    Range("K2:L2").AutoFill Destination:=Range("K2:L" & dataformulas)
    
    Sheets("Closed Address Formatting").Select
    Range("E6").Select
    
End Sub

Open in new window

0
dabug80
Asked:
dabug80
1 Solution
 
dabug80Author Commented:
Nevermind. I figured it out. I had to change the name of the 'ws' reference to 'ws2'. See successful code:

Sub mac_quickformulas()
'
' mac_quickformulas Macro
'
Dim uniqueIDformulas As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Unique ID")
uniqueIDformulas = ws.Range("B" & Rows.Count).End(xlUp).Row

Dim dataformulas As Long
Dim ws2 As Worksheet
Set ws2 = ThisWorkbook.Sheets("Data")
dataformulas = ws2.Range("B" & Rows.Count).End(xlUp).Row

'Add forumula columns to the Unique ID Sheet
    Sheets("Unique ID").Select
    Range("L2").Select
    ActiveCell.FormulaR1C1 = _
        "=COUNTIFS(Data!C[-11],"">0"",Data!C[-6],'Unique ID'!RC[-5],Data!C[-9],"">0"")"
    Range("M2").Select
    ActiveCell.FormulaR1C1 = _
        "=COUNTIFS(Data!C[-12],"">0"",Data!C[-7],'Unique ID'!RC[-6],Data!C[-10],"""")"
    Range("N2").Select
    ActiveCell.FormulaR1C1 = _
        "=(INDEX('Australia Post Codes'!C[-10],MATCH('Unique ID'!RC[-5],'Australia Post Codes'!C[-12],0)))"
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "Region"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "Jobs"
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "Quotes"
    Range("M2").Select
    Range("L2:N2").AutoFill Destination:=Range("L2:N" & uniqueIDformulas)

'Add formula columns to the Data Sheet
    Sheets("Data").Select
    Range("K1").Select
    ActiveWindow.SmallScroll Down:=-6
    ActiveCell.FormulaR1C1 = "Region"
    Range("K2").Select
    ActiveCell.FormulaR1C1 = _
        "=(INDEX('Australia Post Codes'!C[-7],MATCH(RC[-3],'Australia Post Codes'!C[-9],0)))"
    Range("L2").Select
    ActiveCell.FormulaR1C1 = _
        "=(INDEX('Unique ID'!C[-11],MATCH(Data!RC[-6],'Unique ID'!C[-5],0)))"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "ID"
    Range("L2").Select
    Range("K2:L2").AutoFill Destination:=Range("K2:L" & dataformulas)
    
    Sheets("Closed Address Formatting").Select
    Range("E6").Select
    
End Sub

Open in new window

0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now