Dynamic Range

[Jabberwokki]

Hi There And apologies cos i'm sure this has been answered hundreds of times before.

I'm trying to automate the creation of sheets in VBA using sheet names from a dynamic range of cells. The first cell containing a name is B14 and the last cell in the same column is dependent on a numeric value in cell D12 (this can vary).

This is what i've written so far but i'm getting an error when executing the code 'Wrong number of arguments or invalid property assignment'. Any help would be much appreciated.

Regards

Dominic

heres the code..........

Sub Add_Sheets_from_Cell_Value()


Sheets("Front").Select


Dim sRange

Dim eRange

Dim xRange As Range

Dim qq As Range



Set sRange = Range("B14")

eRange = Range("D12").Value



Set xRange = Range(sCell & eCell)



Application.ScreenUpdating = False



For Each qq In xRange

On Error Resume Next ' This will ignore any error

Sheets.Add(After:=Sheets(Sheets.Count)).Name = qq.Value



Next qq



Application.ScreenUpdating = True

End Sub

 

[Fluff]

If you wanted to create sheets using the values in B14:B17 what would the value in D12 be?

 

[Jabberwokki]

If you wanted to create sheets using the values in B14:B17 what would the value in D12 be?

Hi Fluff

Thanks for getting back to me. The start cell for the range is cell B14. D12 is a variable number and i want the VBA to count down the number of cells specified in cell D12 from B14.

Regards

Dominic

 

[Fluff]

Does that mean the answer to my question is 4?

 

[Jabberwokki]

Does that mean the answer to my question is 4?

Apologies....I understand the question now....Yes, correct...4

 

[Fluff]

Ok, how about

Sub jabberwokki()
   Dim Cl As Range
   
   With Sheets("Front")
      For Each Cl In .Range("B14").Resize(.Range("D12").Value - 1)
         If Cl.Value <> "" Then
            If Not Evaluate("isref('" & Cl.Value & "'!A1)") Then
               Sheets.Add(, Sheets(Sheets.Count)).Name = Cl.Value
            End If
         End If
      Next Cl
   End With
End Sub

 

[Jabberwokki]

Ok, how about

Sub jabberwokki()
   Dim Cl As Range
  
   With Sheets("Front")
      For Each Cl In .Range("B14").Resize(.Range("D12").Value - 1)
         If Cl.Value <> "" Then
            If Not Evaluate("isref('" & Cl.Value & "'!A1)") Then
               Sheets.Add(, Sheets(Sheets.Count)).Name = Cl.Value
            End If
         End If
      Next Cl
   End With
End Sub

That works a treat except it produces 1 less sheet than the number specified in cell D12. Is that because of the '-1' in row 5 of the code?

 

[Fluff]

Oops, yes. It needs to be removed.

 

[Jabberwokki]

That works a treat except it produces 1 less sheet than the number specified in cell D12. Is that because of the '-1' in row 5 of the code?

So it should read:

For Each Cl In .Range("B14").Resize(.Range("D12").Value

?

 

[Jabberwokki]

So it should read:

For Each Cl In .Range("B14").Resize(.Range("D12").Value

?

Just tested it and almost there...Just needed the close bracket after '.value'

Works perfectly, thank you.

Regards

Dominic