VBA Adding sheets to a workbook from a list to the end of the workbook

[bhalbach]

I am trying to add sheets to a workbook from a list...

I have it mostly working, it just puts them in the wrong order. I would like them to start at the end of the existing sheets and add to the end in the order of my list.

I have this code but it is erroring out when I try adding this >>>>after:=sheets(sheets,count) I am getting a syntax error
I know I have to count the sheets as they are added so it know where the end is but Im at a loss.

I also want to make it dynamic...so if the list grows the array picks it up and adds sheets accordingly...I havent got to this part yet


Sub addSheets()

Dim BidItem(1 To 15, 1 To 8)
Dim r As Long
Dim c As Long
Dim shNew As Worksheet

For r = 1 To 15
For c = 1 To 8
BidItem(r, c) = Cells(r, c).Value
Next c
Next r

For r = 1 To 15

Set shNew = Worksheets.Add after:=sheets(sheets,count) ' Errors here because of syntax error
shNew.Name = BidItem(r, 3)
shNew.Range("a2").Value = BidItem(r, 3)
shNew.Range("b2").Value = BidItem(r, 4)
shNew.Range("c2").Value = BidItem(r, 5)
shNew.Range("d2").Value = BidItem(r, 6)
Next r

End Sub

 

[Fluff]

It should be

Set shNew = Worksheets.Add(, Sheets(Sheets.Count))

 

[bhalbach]

It should be

Set shNew = Worksheets.Add(, Sheets(Sheets.Count))

Works perfect....thank you.

now I just need to make it dynamic with some conditions.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[bhalbach]

Can I bother you for one more thing?

I have the below code adding the sheets mostly correct...

I would like to NOT create a sheet if column "A" has the text "Header" or "Subtotal". And not be case sensitive in column "A" in case it is spelled in lower case.

Sub addSheets()

    Dim BidItem As Variant
    Dim r As Long
    Dim c As Long
    Dim shNew As Worksheet
    Dim LastRow As Long
   
    With Worksheets("sheet1")
    LastRow = .Cells(Rows.Count, "c").End(xlUp).Row
    BidItem = .Range(.Cells(1, 1), .Cells(LastRow, 8))
    End With
     
    For r = 2 To LastRow
        For c = 1 To 8
            BidItem(r, c) = Cells(r, c).Value
        Next c
    Next r

    For r = 2 To LastRow
       
        Set shNew = Worksheets.Add(, Sheets(Sheets.Count))
        shNew.Name = BidItem(r, 3)
        shNew.Range("a2").Value = BidItem(r, 3)
        shNew.Range("b2").Value = BidItem(r, 4)
        shNew.Range("c2").Value = BidItem(r, 5)
        shNew.Range("d2").Value = BidItem(r, 6)
    Next r
   
End Sub

 

[Fluff]

How about

    For r = 2 To LastRow
       
       Select Case LCase(BidItem(r, 1))
         Case "header", "subtotal"
         Case Else
            Set shNew = Worksheets.Add(, Sheets(Sheets.Count))
            shNew.Name = BidItem(r, 3)
            shNew.Range("a2").Value = BidItem(r, 3)
            shNew.Range("b2").Value = BidItem(r, 4)
            shNew.Range("c2").Value = BidItem(r, 5)
            shNew.Range("d2").Value = BidItem(r, 6)
      End Select
    Next r

 

[bhalbach]

How about

    For r = 2 To LastRow
     
       Select Case LCase(BidItem(r, 1))
         Case "header", "subtotal"
         Case Else
            Set shNew = Worksheets.Add(, Sheets(Sheets.Count))
            shNew.Name = BidItem(r, 3)
            shNew.Range("a2").Value = BidItem(r, 3)
            shNew.Range("b2").Value = BidItem(r, 4)
            shNew.Range("c2").Value = BidItem(r, 5)
            shNew.Range("d2").Value = BidItem(r, 6)
      End Select
    Next r

Awesome thank you...exactly what I wanted!

Can an Array hold formats of cells, columns, text, etc?

What is the fastest way to add new worksheets in the same manner as this code works...but instead of a New Worksheet, it creates new sheets from a template based on a list and fills in the required ranges on the template.

Can that be achieved using an array like this?

Basically, can an array be used to hold all the formats of a "Template" so that the new sheets can be created and filled out with all the formats. Or do I need to copy and add the Template as the new sheets...this would be slow for say 200 sheets to be added.

 

[Fluff]

You would need to copy a template sheet.

 

[bhalbach]

You would need to copy a template sheet.

that too bad...creating the sheets the way this code works is very fast. Thanks

 

[Fluff]

You're welcome & thanks for the feedback.