Simple Copy Procedure returning error

[TheRedCardinal]

I want my macro to run through a range on a user-maintained sheet, and if it meets a criteria, to copy a worksheet with the name stored in the next cell from another open workbook, into this workbook.

Variables are defined and set as follows:

WBk2 = The workbook opened by the macro - working ok, it's open
Wbk1 = The workbook to copy sheets into
WS1 = A Sheet in Wbk1
CellA as a Range

My VBA looks like this:

For Each CellA In WS1.Range("WSCopyData")

    If CellA.Value = "Sales" Then
    
        If DoesSheetExist(Wbk1, CellA.Offset(0, 1)) = False Then
    
            Wbk2.Sheets(CellA.Offset(0, 1)).Copy Before:=WS1
    
        End If

    End If
    
Next CellA

I'm getting a Type Mismatch error on my Copy line.

As always I've done something silly probably with my referencing - can you see it?


PS DoesSheetExist is one of my functions that returns False if the sheet doesn't already exist in Wbk1.

 

[James006]

Hi,
What is WS1 ???
You could test:
Wbk2.Sheets(CellA.Offset(0, 1)).Copy Before:=Wbk2.Sheets("WS1")

 

[TheRedCardinal]

I changed it to this:

For Each CellA In WS1.Range("WSCopyData")

    If CellA.Value = "Sales" Then
   
        If DoesSheetExist(Wbk1, CellA.Offset(0, 1)) = False Then
   
            Wbk2.Sheets(CellA.Offset(0, 1).Value).Copy Before:=WS1
   
        End If

    End If
   
Next CellA

And it worked fine.

 

[James006]

Glad you could fix your problem