Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Visible Sheets to New File
I have a workbook with multiple different views / displays of the sheets
within the file. Most of these pages have deactivate code on the individual sheets. I need to be able to generically copy the visible sheets to a new workbook without taking the code with them. I have tried doing this in an array and can't get it to work, so I am thinking that I need to move it a page at a time. I (personally) could probably make this work by hardcoding the move function to run based upon the selected view, but thought I would see if anyone had a simplier more generic way to select / move the visible sheets. Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Visible Sheets to New File
Try this
Private Sub MoveVisibleSheets() Dim dummyName As String Dim sourceBook As String ' will hold this workbook's name Dim destBook As String ' will hold name of new book Dim otherBook As Workbook Dim ws As Worksheet Dim sheetCount As Integer ' count of sheets in 'otherBook' sourceBook = ThisWorkbook.Name If MsgBox("Are you sure your want to save to new workbook?", _ vbYesNoCancel + vbExclamation) < vbYes Then Exit Sub End If Application.ScreenUpdating = False Workbooks.Add 'adds a "Book#" named workbook - # can vary. destBook = ActiveWorkbook.Name sheetCount = ActiveWorkbook.Worksheets.Count dummyName = "dummy to get this to work on all visible sheets" Set otherBook = Workbooks(destBook) Workbooks(sourceBook).Activate ' back to this book For Each ws In ThisWorkbook.Worksheets If ws.Visible = xlSheetVisible Then If ws.Name < dummyName Then ThisWorkbook.Worksheets(ws.Name).move after:=otherBook.Worksheets(sheetCount) sheetCount = sheetCount + 1 End If End If Next ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Application.ScreenUpdating = True ' it just ends, you can name save the copy with a name of your choice ' and clean up this one for next time out? ' End Sub "Troubled User" wrote: I have a workbook with multiple different views / displays of the sheets within the file. Most of these pages have deactivate code on the individual sheets. I need to be able to generically copy the visible sheets to a new workbook without taking the code with them. I have tried doing this in an array and can't get it to work, so I am thinking that I need to move it a page at a time. I (personally) could probably make this work by hardcoding the move function to run based upon the selected view, but thought I would see if anyone had a simplier more generic way to select / move the visible sheets. Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Visible Sheets to New File
I will try it and get back with you shortly. Thanks for your help.
"Troubled User" wrote: I have a workbook with multiple different views / displays of the sheets within the file. Most of these pages have deactivate code on the individual sheets. I need to be able to generically copy the visible sheets to a new workbook without taking the code with them. I have tried doing this in an array and can't get it to work, so I am thinking that I need to move it a page at a time. I (personally) could probably make this work by hardcoding the move function to run based upon the selected view, but thought I would see if anyone had a simplier more generic way to select / move the visible sheets. Thanks in advance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Visible Sheets to New File
Mike,
A couple of problems. First, it was trying to Move the sheets to the new location rather than copy. I don't know if this was by design or not. I changed this part of the code to be Copy. I ran it with the Copy change and it created the new file, but it took the deactivate code on the pages with it. This code calls generic functions in the source heet and thus errors in the new file. Thoughts? "Troubled User" wrote: I have a workbook with multiple different views / displays of the sheets within the file. Most of these pages have deactivate code on the individual sheets. I need to be able to generically copy the visible sheets to a new workbook without taking the code with them. I have tried doing this in an array and can't get it to work, so I am thinking that I need to move it a page at a time. I (personally) could probably make this work by hardcoding the move function to run based upon the selected view, but thought I would see if anyone had a simplier more generic way to select / move the visible sheets. Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy the same raws of all sheets from about a 100 file to a new sheet of a book and save the file | Setting up and Configuration of Excel | |||
Can I copy just the visible sheets? | Excel Discussion (Misc queries) | |||
Copy Visible Sheets into one Workbook | Excel Programming | |||
Copy visible rows with data to new file | Excel Programming | |||
copy sheets to another file | Excel Programming |