Guys below is a macro I've managed to scrape together which does the following
*
Unhides all hidden sheets within the work book
Copies a Sheets array to other open WB's
Then hides the sheets again in the original WB
The primary macro which isnt shown generates separate WB's one at a time and the names cycle Book1 Book 2 etc...
*
the issue I have is that the Backit2 () sub below works well as a standalone i.e. selected and run from the VBA toolbar however when I add to the main macro and tell it to call Backit2 () it fails at the sheets array section!
*
So works fine alone but fails when called lol :-/
*
I am baffled
*
If any one could take a look through the code and advise where I'm going wrong and any ineficiences that would be great !
*
in addition if anyone could advise how to get the sheets to copy only to open excel WB name begining with "book" followed by a number i.e. book 1 book 2 etc. I would be very greatful!!
*
Macro below
*
Sub backit2()
*** Dim ThisFile As String, x As Long, ww As Window, ws As Worksheet
***
**
For Each ws In Worksheets
*** ws.Visible = xlSheetVisible
*
*********** Next ws
******************* ThisFile = ActiveWindow.Caption
*********************** For Each ww In Windows
******************************* If ww.Visible = True And ww.Caption <> ThisFile Then
**************************************** Sheets(Array("WON", "NROL", "Hospitals West Midlands", "Hospitals Banbury", "SIGBOX-ECR-CONTL", "VMF", "POIC")).Copy after:=Workbooks(ww.Caption).Sheets(Workbooks(ww.Caption).Sheets.Count)
*********************************************** Windows(ThisFile).Activate
**************************************************** ActiveWindow.SelectedSheets.Visible = False
*******************
*** ****************
***********
******* End If
*** Next ww
***
Call Sheetstohide
***
End Sub
Sorry about the * struggle to post from works PC and for some reason on my mobile it always puts the * in lol
Bookmarks