Setting range on different sheet causing error

Question

I am fairly new to VBA and Wondering if someone can help me out.

I have 2 different sheets in a workbook.

Sheet(Raw Data) has a range with Cost Center NameS (Cell BC3 down to empty)

I have to copy Sheet(CC Template) and name it the right 5 characters of Sheet(Raw Data).Range(BC3).Value and change Cell(2,2).value to Sheet(Raw Data).Range(BC3).Value...

Then I want it to go to the next cell in Sheet(Raw Data) ...BC4 and create the second sheet and change the name and Cell(2,2) accordingly until the list in Sheet(Raw Data) ends.

Here is my Code. It creates the first worksheet but then I get run-time Error '1004' at Sheets("Raw Data").Range("BC3").Select in the do until loop. I would like to get rid of X and CCName variable from the code also if possible.

Sub CreateCCTabsinNewPlantFile2()

Dim i As Integer
Dim x As Integer
Dim CCName As String
i = ActiveWorkbook.Worksheets.Count
x = 1

' Select cell BC3, *first line of data*.
      Sheets("Raw Data").Range("BC3").Select

      ' Set Do loop to stop when an empty cell is reached.
      Do Until IsEmpty(ActiveCell)

        CCName = ActiveCell.Value

         ' Code to make worksheets
        Worksheets("CC Template").Copy after:=Worksheets(i)
        ActiveSheet.Name = Right(CCName, 5)
        ActiveSheet.Cells(2, 2).Value = CCName

         ' Step down 1 row from present location.
         Sheets("Raw Data").Range("BC3").Select
         ActiveCell.Offset(x, 0).Select
         x = x + 1

      Loop

End Sub

*"Errors Out"?* Welcome to [so]! Be sure to check out the [tour], as well as [ask]. If you have some _specifics_, about this issue, please [edit] your questions to provide more information and examples (like described here: [mcve]) and share what you've tried & where you've looked so far. Other excellent tips [here](https://codeblog.jonskeet.uk/2010/08/29/writing-the-perfect-question/) — ashleedawg, Feb 26 '18 at 02:06
The problem is that you are using .Select which is causing problems because you switch sheets. Avoid that, see here for how: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — tigeravatar, Feb 26 '18 at 02:17

score 0 · Accepted Answer · answered Feb 26 '18 at 02:15

Sub CreateCCTabsinNewPlantFile2()

Dim i As Integer
Dim X As Integer

X = 3 'Starting row in Sheet("Raw Data")

With ThisWorkbook.Sheets("Raw Data")

    Do Until .Cells(X, 55).Value = "" 'cells(x,55)= BC3. First time x= 3 so Cells(3,55)=BC3
    i = ThisWorkbook.Worksheets.Count 'we update count everytime, because we are adding new sheets
    ThisWorkbook.Worksheets("CC Template").Copy after:=ThisWorkbook.Worksheets(i)

    ThisWorkbook.ActiveSheet.Name = Right(.Cells(X, 55).Value, 5)
    ThisWorkbook.ActiveSheet.Cells(2, 2).Value = .Cells(X, 55).Value

     ' We increade X. That makes check a lower rower in next loop.
     X = X + 1

    Loop

End With
End Sub

Hope this helps.

You get error1004 because you can use Range.Select only in Active Sheet. If you want to Select a Range in different Sheet, first you must Activate that sheet with Sheets("Whatever").Activate.

Also, I Updated your code so you can execute it from any sheet. Your code forces user to have Sheets ("Raw Data") as the ActiveSheet.

Try not use too much Select if you can avoid it. And also , try to get used to Thisworkbook instead of ActiveWorkbook. If you work always in same workbook, is not a problem, but if your macros operate several workbooks, you'll need to difference when to use each one.

score 0 · Answer 2 · answered Feb 26 '18 at 02:17

Try this code

Sub Test()
Dim rng         As Range
Dim cel         As Range

With Sheets("Raw Data")
    Set rng = .Range("BC3:BC" & .Cells(Rows.Count, "BC").End(xlUp).Row)
End With

Application.ScreenUpdating = False
    For Each cel In rng
        If Not SheetExists(cel.Value) Then
            Sheets("CC Template").Copy After:=Sheets(Sheets.Count)
            With ActiveSheet
                .Name = Right(cel.Value, 5)
                .Range("B2").Value = cel.Value
            End With
        End If
    Next cel
    Sheets("Raw Data").Activate
Application.ScreenUpdating = True
End Sub

Function SheetExists(sheetName As String) As Boolean
On Error Resume Next
    SheetExists = (LCase(Sheets(sheetName).Name) = LCase(sheetName))
On Error GoTo 0
End Function

Setting range on different sheet causing error

2 Answers2