Running VBA on hidden worksheets
Results 1 to 10 of 10

Thread: Running VBA on hidden worksheets

  1. #1
    New Member
    Join Date
    Dec 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Running VBA on hidden worksheets

    Hi All,

    I currently run this macro on multiple documents in a folder. What I'd like to do is hide the worksheet - Sheet1 but still allow the macro to run.

    Can anyone help with the below to allow the macro to extract the data from sheet1 whilst the worksheet remains hidden


    Many thanks,


    Paul
    Code:
    Sub LoopThroughDirectory()
    Dim Filepath As String
    Dim erow
    Filepath = Application.ActiveWorkbook.Path
    MyFile = Dir(Filepath + "\*.*")
    
    
    erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    Sheet1.Rows("2:" & Format(erow)).EntireRow.ClearContents
    
    
    Do While Len(MyFile) > 0
    If MyFile = "ZMasterFile.xlsm" Then
    Exit Sub
    End If
    
    
    Application.DisplayAlerts = False
    
    
    Workbooks.Open (Filepath & "" & MyFile)
    Range("A2:M900").Copy
    
    
    ActiveWorkbook.Close
    
    
    
    
    erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    ActiveSheet.Paste Destination:=Worksheets("Raw Data").Range(Cells(erow, 1), Cells(erow, 13))
    
    
    MyFile = Dir
    
    
    Application.DisplayAlerts = True
    
    
    
    
    Loop
    
    
    
    
    End Sub
    Last edited by RoryA; Jun 4th, 2019 at 06:21 AM. Reason: Code tags

  2. #2
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Running VBA on hidden worksheets

    I'm not getting into the detail of what your code actually does, but why not add some code to UNHIDE the sheet before the relevant part of the code, and then add another line to HIDE the sheet again once the main part has finished ?
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  3. #3
    New Member
    Join Date
    Dec 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Running VBA on hidden worksheets

    Quote Originally Posted by Gerald Higgins View Post
    I'm not getting into the detail of what your code actually does, but why not add some code to UNHIDE the sheet before the relevant part of the code, and then add another line to HIDE the sheet again once the main part has finished ?
    That sounds great and exactly what I'm looking for - can you help with what code to use?

    Many thanks

  4. #4
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Running VBA on hidden worksheets

    Perhaps something like this . . .

    Code:
    Sheets("YourSheet").Visible = True
    
    your main code goes here
    
    Sheets("YourSheet").Visible = False
    I'm not a code expert, there may be better ways of doing this.
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  5. #5
    New Member
    Join Date
    Dec 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Running VBA on hidden worksheets

    Tahnks again.................although I can't seem to get that to work
    Last edited by RoryA; Jun 4th, 2019 at 06:22 AM.

  6. #6
    New Member
    Join Date
    Dec 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Running VBA on hidden worksheets

    I've also now noticed that it's actually pulling the data from the last tabbed saved in the workbook rather than referencing Sheet1.

    So if Sheet 1 is the tab saved before closing the workbook the macro works, If it is saved on a different tab then it pulls the wrong data.

    Any help on this would be amazing.

    Currently I use this to extract a line of data out of multiple workbooks to collate the information on one workbook

  7. #7
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,745
    Post Thanks / Like
    Mentioned
    66 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Running VBA on hidden worksheets

    Qualifying the copyFrom range with a reference to the correct sheet should solve your problem.

  8. #8
    New Member
    Join Date
    Dec 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Running VBA on hidden worksheets

    Thank you Yongle - I am new to code, if you could help me I would really appreciate it by directing me to how I should type it?

    Quote Originally Posted by Yongle View Post
    Qualifying the copyFrom range with a reference to the correct sheet should solve your problem.

  9. #9
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,745
    Post Thanks / Like
    Mentioned
    66 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Running VBA on hidden worksheets

    Qualifying the copyFrom range with a reference to the correct sheet should solve your problem.
    replace:
    Code:
    Range("A2:M900").Copy
    with:
    Code:
    Sheets("TheNameOfYourSheet").Range("A2:M900").Copy

  10. #10
    New Member
    Join Date
    Dec 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Running VBA on hidden worksheets

    Yongle it worked like a dream - Thank you so much!!
    Last edited by RoryA; Jun 4th, 2019 at 06:22 AM. Reason: No need to quote everything

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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