Runtime error 1004 when running a macro

[excelnewbie1986]

Hi everyone,

When running a macro, I am getting the following runtime error:

Run-time error '1004':
Method 'Range' of object '_Worksheet' failed

When I click 'Debug', it takes me to the line of code that isn't working, which is highlighted in blue below:

Dim ary As Variant
Dim i As Long
Dim Ws As Worksheet

Set Ws = ActiveSheet
ary = Array("W:W,X:X,V:V,B:B,E:E,F:F,G:G,H:H,I:I,J:J,K:K,L:L,M:M,Y:Y,Z:Z,AH:AH,AI:AI,AJ:AJ,AK:AK,AN:AN,AO:AO,AP:AP,AQ:AQ,AR:AR,AS:AS,AT:AT,AU:AU,AZ:AZ,BA:BA,BL:BL,BM:BM,BN:BN,BO:BO,BZ:BZ,CA:CA,CB:CB,CC:CC", "Priority Families", "W:W,X:X,V:V,E:E,G:G,H:H,I:I,J:J,K:K,L:L,M:M,N:N,P:P,Z:Z,AC:AC,AD:AD,AE:AE,AH:AH,AI:AI,AJ:AJ,AK:AK,AL:AL,AM:AM,AN:AN,AO:AO,AP:AP,AQ:AQ,AR:AR,AS:AS,AT:AT,AU:AU,AV:AV,AW:AW,AX:AX,AY:AY,AZ:AZ,BA:BA,BB:BB,BC:BC,BD:BD,BE:BE,BF:BF,BG:BG,BH:BH,BI:BI,BJ:BJ,BK:BK,BL:BL,BM:BM,BN:BN,BO:BO,BZ:BZ,CA:CA,CB:CB,CC:CC,B:B", "Youth Justice Service")
For i = 0 To UBound(ary) Step 2
Sheets.Add(, Sheets(1)).Name = ary(i + 1)
Ws.Range(ary(i)).Copy Sheets(2).Range("A1")
Next i

The original code was supplied by @Fluff, and I tried to edit it for my own purposes, but have clearly failed!

Any help would be greatly appreciated!

TIA.

 

[JLGWhiz]

Your array needs some work. Each column needs double quotes like this

ary = Array("W:W","X:X","V:V","B:B","E:E","F:F","G:G",,,)

based on how, you are using the array values.

 

[Fluff]

You've got too many values in the 2nd range. Try

Dim ary As Variant
Dim i As Long
Dim Ws As Worksheet

Set Ws = ActiveSheet
ary = Array("V:Z,B:B,E:M,AH:AK,AN:AU,AZ:BA,BL:BO,BZ:CC", "Priority Families", "V:X,E:E,G:N,P:P,Z:Z,AC:AE,AH:CC,B:B", "Youth Justice Service")
For i = 0 To UBound(ary) Step 2
   Sheets.Add(, Sheets(1)).Name = ary(i + 1)
   Ws.Range(ary(i)).Copy Sheets(2).Range("A1")
Next i

 

[JLGWhiz]

Wow, did I misread that one!! (red face)

 

[Fluff]

I had the advantage of having created it originally.

 

[excelnewbie1986]

@Fluff strikes again! Thank you for all your help so far, it's been invaluable. I've been trying to learn on the fly, but getting it all wrong. I can make sense of the changes you've made to the code, so I guess I'm kinda picking stuff up

 

[excelnewbie1986]

Hey @JLGWhiz - thanks for taking the time out to respond. I thought it was to do with the number of array values, but had no idea how to shorten it!

 

[Fluff]

Glad we could help & thanks for the feedback