catharsis50
New Member
- Joined
- Nov 1, 2011
- Messages
- 46
I have created a macro that works just the way I want it. I have copied the code to apply to different sheets in the workbook, change references when necessary. On the other sheets the codes only works for half of the script, when I split it apart it works fine again.
Any suggestions as to why this might be happening? Replicated code below. I made the portion that is not executing bold.
Thanks!
Sub Get_Areacode2()
'
' Get_Areacode Macro2
'
'
Sheets("AC2").Select
Range("E2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-4],3)"
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("E2").AutoFill Destination:=Range("E2:E" & LastRow)
Range("E2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("G3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Range(Selection, Selection.End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo
Range("H3").Select
If WorksheetFunction.CountA(Range("G3:G4")) = 1 Then
Range("H3").Select
ActiveCell.FormulaR1C1 = "=RC[-1]&"","""
Range("I3").Select
ActiveCell.FormulaR1C1 = "=""(""&RC[-2]&"","""
Range("J3").Select
ActiveCell.FormulaR1C1 = "=""+1 ""&RC[-3]&"","""
Range("K3").Select
ActiveCell.FormulaR1C1 = "=""1.""&RC[-4]&"","""
Range("M3").Select
ActiveCell.FormulaR1C1 = "=multicat(Range2)"
Range("M3").Select
Selection.Copy
Sheets("Home").Select
Range("C17").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Else
Sheets("AC2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]&"","""
Range("H3").Select
LastRow2 = Cells(Rows.Count, "G").End(xlUp).Row
Range("H3").AutoFill Destination:=Range("H3:H" & LastRow2)
Range("I3").Select
ActiveCell.FormulaR1C1 = "=""(""&RC[-2]&"","""
Range("I3").Select
LastRow2 = Cells(Rows.Count, "G").End(xlUp).Row
Range("I3").AutoFill Destination:=Range("I3:I" & LastRow2)
Range("J3").Select
ActiveCell.FormulaR1C1 = "=""+1 ""&RC[-3]&"","""
Range("J3").Select
LastRow2 = Cells(Rows.Count, "G").End(xlUp).Row
Range("J3").AutoFill Destination:=Range("J3:J" & LastRow2)
Range("K3").Select
ActiveCell.FormulaR1C1 = "=""1.""&RC[-4]&"","""
Range("K3").Select
LastRow2 = Cells(Rows.Count, "G").End(xlUp).Row
Range("K3").AutoFill Destination:=Range("K3:K" & LastRow2)
Range("M3").Select
ActiveCell.FormulaR1C1 = "=multicat(Range2)"
Range("M3").Select
Selection.Copy
Sheets("Home").Select
Range("C17").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("AC2").Select
Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("M7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Range(Selection, Selection.End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo
Range("N7").FormulaR1C1 = "=VLOOKUP(RC[-1],ST_LKUP!R1C1:R52C3,3,0)"
LastRow3 = Cells(Rows.Count, "M").End(xlUp).Row
Range("N7").AutoFill Destination:=Range("N7:N" & LastRow3)
Range("M5").Select
ActiveCell.FormulaR1C1 = "=multicat(State3)"
Range("M5").Select
Selection.Copy
Sheets("Home").Select
Range("C18").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("AC2").Select
Range("O7").FormulaR1C1 = "=VLOOKUP(RC[-2],ST_LKUP!R1C1:R52C3,2,0)"
Range("O7").AutoFill Destination:=Range("O7:O" & LastRow3)
Range("M4").Select
ActiveCell.FormulaR1C1 = "=multicat(State4)"
Range("M4").Select
Selection.Copy
Sheets("Home").Select
Range("C19").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
'Get Zipcode Portion
Sheets("ZC2").Select
Range("I4").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-8],3)"
LastRow4 = Cells(Rows.Count, "A").End(xlUp).Row
Range("I4").AutoFill Destination:=Range("I4:I" & LastRow4)
Range("I4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("K4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Range(Selection, Selection.End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo
Range("L4").Select
If WorksheetFunction.CountA(Range("K4:K5")) = 1 Then
Range("K4").Select
Selection.Copy
Sheets("Home").Select
Range("C16").Select
ActiveSheet.Paste
Else
ActiveCell.FormulaR1C1 = "=RC[-1]&"","""
Range("L4").Select
LastRow5 = Cells(Rows.Count, "K").End(xlUp).Row
Range("L4").AutoFill Destination:=Range("L4:L" & LastRow5)
Range("L4").Select
Selection.End(xlDown).Select
Selection.Offset(, -1).Select
Selection.Copy
Selection.Offset(, 1).Select
ActiveSheet.Paste
Range("M4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=multicat(MultiC3)"
Range("M4").Select
Selection.Copy
Sheets("Home").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Any suggestions as to why this might be happening? Replicated code below. I made the portion that is not executing bold.
Thanks!
Sub Get_Areacode2()
'
' Get_Areacode Macro2
'
'
Sheets("AC2").Select
Range("E2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-4],3)"
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("E2").AutoFill Destination:=Range("E2:E" & LastRow)
Range("E2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("G3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Range(Selection, Selection.End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo
Range("H3").Select
If WorksheetFunction.CountA(Range("G3:G4")) = 1 Then
Range("H3").Select
ActiveCell.FormulaR1C1 = "=RC[-1]&"","""
Range("I3").Select
ActiveCell.FormulaR1C1 = "=""(""&RC[-2]&"","""
Range("J3").Select
ActiveCell.FormulaR1C1 = "=""+1 ""&RC[-3]&"","""
Range("K3").Select
ActiveCell.FormulaR1C1 = "=""1.""&RC[-4]&"","""
Range("M3").Select
ActiveCell.FormulaR1C1 = "=multicat(Range2)"
Range("M3").Select
Selection.Copy
Sheets("Home").Select
Range("C17").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Else
Sheets("AC2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]&"","""
Range("H3").Select
LastRow2 = Cells(Rows.Count, "G").End(xlUp).Row
Range("H3").AutoFill Destination:=Range("H3:H" & LastRow2)
Range("I3").Select
ActiveCell.FormulaR1C1 = "=""(""&RC[-2]&"","""
Range("I3").Select
LastRow2 = Cells(Rows.Count, "G").End(xlUp).Row
Range("I3").AutoFill Destination:=Range("I3:I" & LastRow2)
Range("J3").Select
ActiveCell.FormulaR1C1 = "=""+1 ""&RC[-3]&"","""
Range("J3").Select
LastRow2 = Cells(Rows.Count, "G").End(xlUp).Row
Range("J3").AutoFill Destination:=Range("J3:J" & LastRow2)
Range("K3").Select
ActiveCell.FormulaR1C1 = "=""1.""&RC[-4]&"","""
Range("K3").Select
LastRow2 = Cells(Rows.Count, "G").End(xlUp).Row
Range("K3").AutoFill Destination:=Range("K3:K" & LastRow2)
Range("M3").Select
ActiveCell.FormulaR1C1 = "=multicat(Range2)"
Range("M3").Select
Selection.Copy
Sheets("Home").Select
Range("C17").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("AC2").Select
Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("M7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Range(Selection, Selection.End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo
Range("N7").FormulaR1C1 = "=VLOOKUP(RC[-1],ST_LKUP!R1C1:R52C3,3,0)"
LastRow3 = Cells(Rows.Count, "M").End(xlUp).Row
Range("N7").AutoFill Destination:=Range("N7:N" & LastRow3)
Range("M5").Select
ActiveCell.FormulaR1C1 = "=multicat(State3)"
Range("M5").Select
Selection.Copy
Sheets("Home").Select
Range("C18").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("AC2").Select
Range("O7").FormulaR1C1 = "=VLOOKUP(RC[-2],ST_LKUP!R1C1:R52C3,2,0)"
Range("O7").AutoFill Destination:=Range("O7:O" & LastRow3)
Range("M4").Select
ActiveCell.FormulaR1C1 = "=multicat(State4)"
Range("M4").Select
Selection.Copy
Sheets("Home").Select
Range("C19").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
'Get Zipcode Portion
Sheets("ZC2").Select
Range("I4").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-8],3)"
LastRow4 = Cells(Rows.Count, "A").End(xlUp).Row
Range("I4").AutoFill Destination:=Range("I4:I" & LastRow4)
Range("I4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("K4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Range(Selection, Selection.End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo
Range("L4").Select
If WorksheetFunction.CountA(Range("K4:K5")) = 1 Then
Range("K4").Select
Selection.Copy
Sheets("Home").Select
Range("C16").Select
ActiveSheet.Paste
Else
ActiveCell.FormulaR1C1 = "=RC[-1]&"","""
Range("L4").Select
LastRow5 = Cells(Rows.Count, "K").End(xlUp).Row
Range("L4").AutoFill Destination:=Range("L4:L" & LastRow5)
Range("L4").Select
Selection.End(xlDown).Select
Selection.Offset(, -1).Select
Selection.Copy
Selection.Offset(, 1).Select
ActiveSheet.Paste
Range("M4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=multicat(MultiC3)"
Range("M4").Select
Selection.Copy
Sheets("Home").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If