Method 'Range' of object '_Global' Failed error VBA when selecting Range

Question

So I'm using this macro to extract only the data that I need to perform a regression, and the sample set includes quite a bit of useless information for my needs. When I try to select the columns from the CCDetail workbook- I get the "'Range' of object '_Global' Failed" error. Any ideas on why this is?

Sub ExtractCCDetail()

Dim WorkbookName As String
Dim CCDetail As Workbook
Dim Harvester As Workbook
Dim RAWData As Worksheet
Set CCDetail = Workbooks(2)
Set Harvester = ThisWorkbook

WorkbookName = CCDetail.Name
CCDetail.Activate
Set RAWData = Worksheets("Department Totals")
RAWData.Select
    'This is where the code will break
    Range( _
        "D:D,E:E,F:F,M:M,X:X,Y:Y,Z:Z,AA:AA,AC:AC,AD:AD,AE:AE,AF:AF,BD:BD,BF:BF," _
        ).Select
        Selection.Copy
    Harvester.Activate
    Sheets.Add After:=Sheets(Worksheets.Count), Count:=1
    ActiveSheet.Name = WorkbookName
    ActiveSheet.Paste
End Sub

*As a side note- I have tested doing this manually and I don't have a problem. I have also tried calling

ActiveSheet.Range (wanted Range)

RAWData.Range (wanted range)

But neither of these have worked.

you have a spare comma at the end of your range... `BF:BF,"` — SeanC, Jan 02 '13 at 20:23
You should avoid using select all together and just copy the range immediately [check this link for more info](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — scott, Jan 02 '13 at 20:28
Thanks for the great reading Scott- I'm very new to this, and I'll be reading this in my free time. — Jake, Jan 02 '13 at 20:33

score 5 · Accepted Answer · answered Jan 02 '13 at 20:23

The problem is that you have an extra comma in the arguments for your Range. Try this instead:

Range("D:D,E:E,F:F,M:M,X:X,Y:Y,Z:Z,AA:AA,AC:AC,AD:AD,AE:AE,AF:AF,BD:BD,BF:BF").Copy

If you want to clean up your code a bit and avoid Selects and such, try this out:

Sub ExtractCCDetail()

Dim WorkbookName As String
Dim CCDetail As Workbook
Dim Harvester As Workbook
Dim RAWData As Worksheet

Set CCDetail = Workbooks(2)
Set Harvester = ThisWorkbook

WorkbookName = CCDetail.Name

Set RAWData = CCDetail.Worksheets("Department Totals")

RAWData.Range("D:D,E:E,F:F,M:M,X:X,Y:Y,Z:Z,AA:AA,AC:AC,AD:AD,AE:AE,AF:AF,BD:BD,BF:BF").Copy

Dim wksCopy As Worksheet
Set wksCopy = Harvester.Sheets.Add(After:=Sheets(Worksheets.Count), Count:=1)

With wksCopy
    .Paste
    .Name = WorkbookName
End With

End Sub

Wow. I feel like a moron. I did that and it worked like a charm. — Jake, Jan 02 '13 at 20:28

Method 'Range' of object '_Global' Failed error VBA when selecting Range

1 Answers1