Results 1 to 15 of 15

Macro runs well but not when called by another ??

Threaded View

  1. #1
    Registered User
    Join Date
    10-15-2012
    Location
    Birmingham
    MS-Off Ver
    Excel 2003
    Posts
    81

    Macro runs well but not when called by another ??

    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
    Last edited by james 35; 02-12-2013 at 08:53 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1