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.

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.

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.

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.