Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying worksheets to a new workbook
I am using Excel 2000
I have a macro which works of the form: Sheets(Array("Sheet2", "Sheet3")).Select Sheets(Array("Sheet2", "Sheet3")).Copy Which creates a new workbook with sheets 1 and 2 in it. The real-life example has about 20 sheets and it is difficult t o maintain if I add or re-name sheets. I want to be able to pick up a list of sheets that I want to copy from the workbook and put it in place of the "sheet2" etc. Any ideas ? I am trying to do it by creating an array and copying the sheets one at a time, but it seems very cumbersome compared with the starting point above. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying worksheets to a new workbook
Worksheets.Copy
copies all the worksheets. If you want to build an array of less than all the sheets, what is the criteria to determine which sheets to copy. -- Regards, Tom Ogilvy "John Turton" wrote in message ... I am using Excel 2000 I have a macro which works of the form: Sheets(Array("Sheet2", "Sheet3")).Select Sheets(Array("Sheet2", "Sheet3")).Copy Which creates a new workbook with sheets 1 and 2 in it. The real-life example has about 20 sheets and it is difficult t o maintain if I add or re-name sheets. I want to be able to pick up a list of sheets that I want to copy from the workbook and put it in place of the "sheet2" etc. Any ideas ? I am trying to do it by creating an array and copying the sheets one at a time, but it seems very cumbersome compared with the starting point above. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying worksheets to a new workbook
Sub aacopysheets()
Dim varr As Variant Dim i As Long Dim sh As Worksheet ReDim varr(0 To ThisWorkbook.Worksheets.Count - 1) i = 0 For Each sh In ThisWorkbook.Worksheets If InStr(1, sh.Range("A1").Value, "report", vbTextCompare) Then varr(i) = sh.Name i = i + 1 End If Next ReDim Preserve varr(0 To i - 1) Worksheets(varr).Copy End Sub In the above example, any sheet that has the word report contained in the value of cell A1 is copied. -- Regards, Tom Ogilvy "John Turton" wrote in message ... What sort of criteria could I use ? At the moment I am copying a specific set of worksheets containing the assumptions and results of a model. I don't want to copy the sheets containing the model itself since the whole model is 13Mb. I want to have a set of sheets containing the assumptions that just get copied out of the original workbook and a second set of sheets that get copied out of the workbook and then copied and pasted as values. None of this is difficult if all the sheets keep their names and none are added, but the model is still being developed and I am likely to create new sheets which I want to add to a list rather than editing a long list in vba. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copying data to different worksheets in the same workbook | Excel Worksheet Functions | |||
copying worksheets in a workbook limit? | Excel Worksheet Functions | |||
copying multiple worksheets to a new workbook | Excel Discussion (Misc queries) | |||
Copying worksheets to a new workbook | Excel Discussion (Misc queries) | |||
copying addresses from one workbook to another into several worksheets | Excel Worksheet Functions |