Excel 2010 - Select method of range class failed

Question

I want to run the following vba code:

Sub combineSheets()

Dim rngPaste As Range 'range to paste to
Dim rngCopy As Range 'range to copy from
Dim wb As Excel.Workbook


Dim strRange As String 'range in sheets to copy from
strRange = "A2:A10"

Set rngPaste = ActiveWorkbook.Worksheets("Combined").Range(strRange) 'initial range to paste into

Set wb = ActiveWorkbook
Dim s As Integer
For s = 2 To Sheets.Count

 'copy down the value
    wb.Worksheets(s).Range("A:A").Select ' ERROR: here I get it!
    wb.Worksheets(s).Range("A7").Activate
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    wb.Worksheets(s).Rows("4:4").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Selection.UnMerge
    wb.Worksheets(s).Range("B4").Select
    Selection.Copy
    wb.Worksheets(s).Range("A5").Select
    ActiveSheet.Paste
    wb.Worksheets(s).Range("A6").Select
    ActiveSheet.Paste
    wb.Worksheets(s).Range("A7").Select
    ActiveSheet.Paste
    wb.Worksheets(s).Range("A8").Select
    ActiveSheet.Paste
    wb.Worksheets(s).Range("A9").Select
    ActiveSheet.Paste
    wb.Worksheets(s).Range("A10").Select
    ActiveSheet.Paste
    'delete the first 4 rows
    wb.Worksheets(s).Rows("1:4").Select
    Selection.Delete Shift:=xlUp
    '-------------------

    'copy to new sheet
    Set rngCopy = ActiveWorkbook.Worksheets(s).Range(strRange) 'copy from same range in each sheet

    rngPaste.Value = rngCopy.Value 'copy values into first sheet

    Set rngPaste = rngPaste.Offset(10, 0) 'moves paste range for next copy

Next s

End Sub

However, I get the error: Run-time error 1004: Select method of Range class failed.

Any suggestion what I am doing wrong?

I appreciate your reply!

You may want to see [THIS](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — Siddharth Rout, Jul 02 '15 at 08:40
Try to debug step by step, and add watch on `wb.worksheets(s).Range("A:A")`. And try to avoid `.Select` to have a cleaner code — ZwoRmi, Jul 02 '15 at 08:42

score 2 · Answer 1 · answered Jul 02 '15 at 08:47

2

You cant use select method like that

wb.Worksheets(s).Range("A:A").Select ' ERROR: here I get it!

You should activate workbook, then select worksheet and range like this:

wb.Activate
Worksheets(s).Select
Range("A:A").Select

answered Jul 02 '15 at 08:47

noas

71
7

score 1 · Accepted Answer · answered Jul 02 '15 at 09:07

You cannot select a cell or range in a worksheet which is not in the foreground. Instead you must bring the sheet into the foreground by activating it beforehand. So you should add

ActiveWorkbook.Worksheets(s).Activate

as the first line in the for loop. Like this:

For s = 2 To Sheets.Count

    ActiveWorkbook.Worksheets(s).Activate  ' <--- add the line here

 'copy down the value
    wb.Worksheets(s).Range("A:A").Select ' ERROR: here I get it!
    wb.Worksheets(s).Range("A7").Activate
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    wb.Worksheets(s).Rows("4:4").Select
 ' [...]

Excel 2010 - Select method of range class failed

2 Answers2