add sheets

[Guest]

hello their, um using vb2005 and have a tricky question
hope im on the right place

im trying to fill an excel file with data, i have a model file, and then i
want to fill the model and save it as another file, but i wanted to make it
with a for cycle.

the point is to make for example 5 times, grab the model sheet from the
model file, add it 5 times to the new file and fill it with data

the final result of that its 5 sheets all with the same formating with
diferrent data and do not mix with the model file

this is the code that i have:

Dim oExcel As Excel.Application

Dim oBooks As Excel.Workbooks

Dim oBook As Excel.Workbook

Dim oSheet As Excel.Worksheet

oExcel = CreateObject("Excel.Application")

oBooks = oExcel.Workbooks

oBook = oBooks.Add(My.Application.Info.DirectoryPath & "\ModeloRelatorio.xls")

Dim n As Integer

For n = 1 To Ds1._Empregados.Rows.Count - 1

oSheet = oBook.Sheets(1).copy(1, 1)

next



with a diferent code i can add it 5 times but not it doesnt make the copy
from that 5 sheet of the model sheet

hope you can understand my problem and give me the soluiton

 

[ljsmith]

My recommendation would be to create a template that you'd use to creat
the number of copies you need and then just save the workbook with th
new worksheets under another name.

The code below is how I've handled copying a master sheet, depending o
a value in a specified worksheet & cell.

Public Sub addSheet()
' This macro will copy a worksheet a variable number of times an
moving
' the new worksheet to the end of the other worksheets in the workbook
Call setLang ' sets the language if necessary
' Set the Reference Number in all worksheets (where applicable)
Call setInfo
' unlock the workbook to allow pages to be added
Call unlockWkBook
' make the worksheet visible
If Sheet1.Visible = xlSheetHidden Then
Sheet1.Visible = xlSheetVisible
End If
' select the page to be copied
Sheet1.Select
' declare the variables, number of pages required plus the numbe
of pages
' existing
addWkSheet = Sheet2.Range("A44").Value
' this variable will take the number generated above and add
worksheet
' to it for the Incident report, the first page is 1, all witnes
reports make
' up the balance of the workbook, therefore its 1+ the number of
' witnesses. If there are other sheets within the workbook you wan
plus
' new copies, the "+1" should be adjusted as necessary
newSheetCount = addWkSheet + 1
' this provides the variable for the final number of worksheets i
the
' workbook and tells the loop when to stop
counter = newSheetCount
' this loop will run until the last worksheet equals the number o
worksheets
' required.
Do Until counter = Worksheets.Count
' this statement indicates which page to be copied and where t
place
' the copied worksheet (last)
Sheet1.Copy After:=Sheets(Worksheets.Count)
' this statement tells the sub to return to the Do Until point an
repeat.
' Loop puts the focus on the first Worksheet (Master)
Sheet1.Select
Sheet1.Visible = xlSheetHidden
' this statement locks the workbook so no further changes can b
made.
Sheets("Workbook(2)").Select
Call lockWkBook

End Su