Setting excel VBA Range from multiple variables

Question

I'm having trouble with the Range function. (Nearly) completed code below. I'm fairly new to VBA, so please explain the basics if you have the time. This is the line that is giving me a debug error:

Set CombinedPropRange = ThisWorkbook.Worksheets("PropFiltered").Range("A" & _ 
  PropACount & ":J" & SplitTabName(2))

Full Code Below:

Sub FillTabsTest()
' FillTabsTest Macro

HowManyTabsDoYouNeed = 4 'If you want to add or remove Tabs, you must change this number AND add/subtract from the "TabName(1)" section below.

ReDim TabName(1 To HowManyTabsDoYouNeed) As String

'Grabs Data from Original Workbook and creates a new Workbook.
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range("A1:P1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Call WrapText


TabName(1) = "April H.,0,1000"
TabName(2) = "Christopher H.,0,1000"
TabName(3) = "Christie E.,500,500"
TabName(4) = "Cori M.,500,500"

    'Places Filtered Auto Events on its own tab
Sheets("Sheet1").Select
Cells.Select
Selection.AutoFilter
ActiveSheet.Range("$A:$S").AutoFilter Field:=8, Criteria1:="=COMAUTO", _
    Operator:=xlOr, Criteria2:="=PERSAUTO"
ActiveSheet.Range("$A:$S").AutoFilter Field:=5, Criteria1:="=3*", Operator _
    :=xlAnd
ActiveSheet.Range("$A:$S").AutoFilter Field:=9, Criteria1:=Array( _
    "AUTO BODILY INJURY", "AUTO MED PAY", "AUTO PROPERTY DAMAGE", "AUTO-ENDORSEMENT", _
    "AUTO-OTHER", "BODILY INJURY", "COLLISION", "COMPREHENSIVE", "LIABILITY", "OTHER", _
    "RENTAL REIMBURSEMENT", "UM/UIM"), Operator:=xlFilterValues
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "AutoFiltered"
ActiveSheet.Paste

'Places Filtered Property Events on its own tab
Sheets("Sheet1").Select
Cells.Select
Application.CutCopyMode = False
ActiveSheet.ShowAllData
Cells.Select
ActiveSheet.Range("$A:$S").AutoFilter Field:=8, Criteria1:="=COMPROP", _
    Operator:=xlOr, Criteria2:="=PLPROP"
ActiveSheet.Range("$A:$S").AutoFilter Field:=5, Criteria1:="=3*", Operator _
    :=xlAnd
ActiveSheet.Range("$A:$S").AutoFilter Field:=12, Criteria1:="<>*FIRE*", _
    Operator:=xlOr, Criteria2:="<>*SMOKE*"
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "PropFiltered"
ActiveSheet.Paste




'Begin adding the above named tabs to the workbook
For i = 1 To HowManyTabsDoYouNeed
    Sheets.Add After:=Sheets(Sheets.Count)
    SplitTabName = Split(TabName(i), ",")
    ActiveSheet.Name = SplitTabName(0)
Next i

 'Begin populating employee's sheets.

Dim AutoACount As Integer
Dim PropACount As Integer
' Dim AutoAPasteCount As Integer
Dim PropAPasteCount As Integer
Dim AutoJCount As Integer
Dim PropJCount As Integer
'Dim AutoRangeA As Range
'Dim AutoRangeJ As Range
'Dim PropRangeA As Range
'Dim PropRangeJ As Range
Dim PropAPasteCountRange As String
Dim CombinedPropRange As Range
Dim CombinedAutoRange As Range


AutoACount = 2
PropACount = 2
AutoJCount = 2
PropJCount = 2
PropAPasteCount = 2

For i = 1 To HowManyTabsDoYouNeed
    SplitTabName = Split(TabName(i), ",")
    If SplitTabName(1) <> "0" Then
       ' Set AutoRangeA = Range("A" & AutoACount)
       ' Set AutoRangeJ = Range("J" & SplitTabName(1))
        Sheets("AutoFiltered").Select
        Set CombinedAutoRange = ThisWorkbook.Worksheets("AutoFiltered").Range("A" & AutoACount & ":J" & SplitTabName(1))
        CombinedAutoRange.Copy
        Sheets("SplitTabName(0)").Select
        ActiveSheet.Paste
        AutoACount = AutoACount + SplitTabName(1)
        PropAPasteCount = SplitTabName(1)
    End If
    If SplitTabName(2) <> "0" Then
        'Set PropRangeA = Range("A" & PropACount)
        'MsgBox PropRangeA
        'Set PropRangeJ = Range("J" & SplitTabName(2))
        PropAPasteCountRange = "A" & PropAPasteCount
        'Sheets("PropFiltered").Select
        Set CombinedPropRange = ThisWorkbook.Worksheets     ("PropFiltered").Range("A" & PropACount & ":J" & SplitTabName(2))
        CombinedPropRange.Copy
        Sheets("SplitTabName(0)").Select
        ThisWorkbook.Worksheets(SplitTabName(0)).Cells(PropAPasteCountRange).Select
        ActiveSheet.Paste
        PropACount = PropACount + SplitTabName(2)
    End If
Next i

End

At a quick glance, I can see you would benefit by reading through [how to avoid using `.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). — BruceWayne, Jun 09 '16 at 21:08
Two lines down, `Sheets("SplitTabName(0)").Select` is also going to cause problems. — , Jun 09 '16 at 21:10
I'll read over the link provided and modify the code to avoid Select (or try to) and post back here with a new version. Thank you both for the help so far. — TheFuzz77, Jun 10 '16 at 03:38

Setting excel VBA Range from multiple variables

0 Answers0