Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
add sheets
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
add sheets
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 -- ljsmit ----------------------------------------------------------------------- ljsmith's Profile: http://www.excelforum.com/member.php...fo&userid=3053 View this thread: http://www.excelforum.com/showthread.php?threadid=54668 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA | Excel Worksheet Functions | |||
How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Worksheet Functions | |||
How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Programming | |||
Macro for filter on protected workbook that works for all sheets, no matter what sheets are named? | Excel Programming | |||
Allocate Files to Sheets and Build a Master Sheet which Summarises All Sheets | Excel Programming |