Hi,
I am parsing a text file into component parts:

I want to open a new workbook, add a few worksheets, ad data, save file.
I can add the workbook but I can't seem to add worksheets to it

How can I fix the code below?
Thanks
Habib

----------------------
SrcFile = GetFile()
XLFile = Left(srcFile, Len(srcFile) - 4) & ".xls"
Set wkbook = Workbooks.Add()

wkbook.Activate

wksheets = Array("Invoice", "Payment", "Reference", "Summary", "Other")
For i = 0 To UBound(wksheets)
Worksheets.Add(Before:=Worksheets(Worksheets.Count)).Name = "test"
ActiveSheet.Name = wksheets(i)
Next

I'd use:

Dim wkSheets As Variant
Dim i As Long
Dim wkBook As Workbook

Set wkBook = Workbooks.Add(1) 'single sheet
wkBook.Worksheets(1).Name = "deletemelater"

wkSheets = Array("Invoice", "Payment", "Reference", "Summary", "Other")
For i = LBound(wkSheets) To UBound(wkSheets)
With wkBook
.Worksheets.Add(Before:=.Worksheets(Worksheets.Count)).Name = wkSheets(i)
End With
Next i

Application.DisplayAlerts = False
wkBook.Worksheets("deletemelater").Delete
Application.DisplayAlerts = True



"HSalim[MVP]" wrote:
>
> Hi,
> I am parsing a text file into component parts:
>
> I want to open a new workbook, add a few worksheets, ad data, save file.
> I can add the workbook but I can't seem to add worksheets to it
>
> How can I fix the code below?
> Thanks
> Habib
>
> ----------------------
> SrcFile = GetFile()
> XLFile = Left(srcFile, Len(srcFile) - 4) & ".xls"
> Set wkbook = Workbooks.Add()
>
> wkbook.Activate
>
> wksheets = Array("Invoice", "Payment", "Reference", "Summary", "Other")
> For i = 0 To UBound(wksheets)
> Worksheets.Add(Before:=Worksheets(Worksheets.Count)).Name = "test"
> ActiveSheet.Name = wksheets(i)
> Next
>
>
>

--

Dave Peterson

There's a typo in my code!!!

..Worksheets.Add(Before:=.Worksheets(Worksheets.Count)).Name = wkSheets(i)
should be:
..Worksheets.Add(Before:=.Worksheets(.Worksheets.Count)).Name = wkSheets(i)

Those leading dots mean that those items belong to the object in the previous
"With" statement.



Dave Peterson wrote:
>
> I'd use:
>
> Dim wkSheets As Variant
> Dim i As Long
> Dim wkBook As Workbook
>
> Set wkBook = Workbooks.Add(1) 'single sheet
> wkBook.Worksheets(1).Name = "deletemelater"
>
> wkSheets = Array("Invoice", "Payment", "Reference", "Summary", "Other")
> For i = LBound(wkSheets) To UBound(wkSheets)
> With wkBook
> .Worksheets.Add(Before:=.Worksheets(Worksheets.Count)).Name = wkSheets(i)
> End With
> Next i
>
> Application.DisplayAlerts = False
> wkBook.Worksheets("deletemelater").Delete
> Application.DisplayAlerts = True
>
> "HSalim[MVP]" wrote:
> >
> > Hi,
> > I am parsing a text file into component parts:
> >
> > I want to open a new workbook, add a few worksheets, ad data, save file.
> > I can add the workbook but I can't seem to add worksheets to it
> >
> > How can I fix the code below?
> > Thanks
> > Habib
> >
> > ----------------------
> > SrcFile = GetFile()
> > XLFile = Left(srcFile, Len(srcFile) - 4) & ".xls"
> > Set wkbook = Workbooks.Add()
> >
> > wkbook.Activate
> >
> > wksheets = Array("Invoice", "Payment", "Reference", "Summary", "Other")
> > For i = 0 To UBound(wksheets)
> > Worksheets.Add(Before:=Worksheets(Worksheets.Count)).Name = "test"
> > ActiveSheet.Name = wksheets(i)
> > Next
> >
> >
> >
>
> --
>
> Dave Peterson

--

Dave Peterson

Dave,
thanks for that. That did the trick.

While tinkering with the code, I found another way.

wkSheets = Array("Invoice", "Payment", "Reference", "Summary", "Other")

Application.SheetsInNewWorkbook = UBound(wkSheets) + 1
Set wkBook = Workbooks.Add
For i = LBound(wkSheets) To UBound(wkSheets)
With wkBook
.Sheets(i + 1).Name = wkSheets(i)
End With
Next i


Regards
Habib



"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> There's a typo in my code!!!
>
> .Worksheets.Add(Before:=.Worksheets(Worksheets.Count)).Name = wkSheets(i)
> should be:
> .Worksheets.Add(Before:=.Worksheets(.Worksheets.Count)).Name = wkSheets(i)
>
> Those leading dots mean that those items belong to the object in the
> previous
> "With" statement.
>
>
>
> Dave Peterson wrote:
>>
>> I'd use:
>>
>> Dim wkSheets As Variant
>> Dim i As Long
>> Dim wkBook As Workbook
>>
>> Set wkBook = Workbooks.Add(1) 'single sheet
>> wkBook.Worksheets(1).Name = "deletemelater"
>>
>> wkSheets = Array("Invoice", "Payment", "Reference", "Summary", "Other")
>> For i = LBound(wkSheets) To UBound(wkSheets)
>> With wkBook
>> .Worksheets.Add(Before:=.Worksheets(Worksheets.Count)).Name =
>> wkSheets(i)
>> End With
>> Next i
>>
>> Application.DisplayAlerts = False
>> wkBook.Worksheets("deletemelater").Delete
>> Application.DisplayAlerts = True
>>
>> "HSalim[MVP]" wrote:
>> >
>> > Hi,
>> > I am parsing a text file into component parts:
>> >
>> > I want to open a new workbook, add a few worksheets, ad data, save
>> > file.
>> > I can add the workbook but I can't seem to add worksheets to it
>> >
>> > How can I fix the code below?
>> > Thanks
>> > Habib
>> >
>> > ----------------------
>> > SrcFile = GetFile()
>> > XLFile = Left(srcFile, Len(srcFile) - 4) & ".xls"
>> > Set wkbook = Workbooks.Add()
>> >
>> > wkbook.Activate
>> >
>> > wksheets = Array("Invoice", "Payment", "Reference", "Summary", "Other")
>> > For i = 0 To UBound(wksheets)
>> > Worksheets.Add(Before:=Worksheets(Worksheets.Count)).Name = "test"
>> > ActiveSheet.Name = wksheets(i)
>> > Next
>> >
>> >
>> >
>>
>> --
>>
>> Dave Peterson
>
> --
>
> Dave Peterson