Sheets are not visible after the macro runs like it should be.

J

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
443
Office Version
  1. 2019
Platform
  1. Windows
I have a macro where I hide sheets "Core Report" and "Joints" pretty early. This is the end of the macro. After exporting it as a PDF i would like the sheets to be visible again. When I run this they stay hidden. I've also tried moving the sheets visible code before the End With


VBA Code: 
   With srcWB
        fName = srcWB.Sheets("A").Range("A!F19").Value
        
        
           Sheets(Array("A", "Sheet2")).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "C:\Users\jdavis\Dropbox\Quality Control\Asphalt\Asphalt Reports\" & fName, _
        openafterpublish:=True, ignoreprintareas:=False


    End With
   
    
    Sheets("Core Report").Visible = True
    Sheets("Joints").Visible = True
       End If
   
End Sub
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
Try qualifying your sheet references. For example, if those sheets are located in Book1.xlsm...

VBA Code: 
        Workbooks("Book1.xlsm").Sheets("Core Report").Visible = True
        Workbooks("Book1.xlsm").Sheets("Joints").Visible = True

Hope this helps!
 
Upvote 0
Well, i thought it had done the trick. It took a few seconds after the pdf displayed for them to be hidden.
 
Upvote 0
In that case, I would suggest that you post all of the relevant code so that we can have a look and try to help.
 
Upvote 0
VBA Code: 
Option Explicit

Sub Open_Workbook()

    Dim srcWB As Workbook
    Dim destWB As Workbook
    Dim fName As String
    Dim lastRow As Long
   
'   Capture current workbook as source workbook
    Set srcWB = ActiveWorkbook

'   Open destination workbook and capture it as destination workbook
    Workbooks.Open "C:\Users\jdavis\Dropbox\Quality Control\Asphalt\Misc\Yearly HMA Charts.xlsx"
    Set destWB = ActiveWorkbook
'   hide_Multiple_Sheets()
    srcWB.Sheets("Core Report").Visible = False
    srcWB.Sheets("Joints").Visible = False
'   Unhide_Multiple_Sheets()
    destWB.Sheets("Samples").Visible = True
    destWB.Sheets("Sieves").Visible = True

'   Find last row of Sieve data in destination workbook
    lastRow = destWB.Sheets("Sieves").Cells(Rows.Count, "G").End(xlUp).Row + 1
   
'   Copy Sieve data from source workbook to destination workbook
    srcWB.Sheets("A").Range("G20").Copy
    destWB.Sheets("Sieves").Range("A" & lastRow).Resize(12, 1).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("H20").Copy
    destWB.Sheets("Sieves").Range("B" & lastRow).Resize(12, 1).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("G3").Copy
    destWB.Sheets("Sieves").Range("C" & lastRow).Resize(12, 1).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("G5").Copy
    destWB.Sheets("Sieves").Range("D" & lastRow).Resize(12, 1).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("B6").Copy
    destWB.Sheets("Sieves").Range("E" & lastRow).Resize(12, 1).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("A10:A21").Copy
    destWB.Sheets("Sieves").Range("F" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("D10:D21").Copy
    destWB.Sheets("Sieves").Range("G" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("G21:G32").Copy
    destWB.Sheets("Sieves").Range("H" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("H21:H32").Copy
    destWB.Sheets("Sieves").Range("I" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("D22").Copy
    destWB.Sheets("Sieves").Range("J" & lastRow).Resize(12, 1).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("G47").Copy
    destWB.Sheets("Sieves").Range("K" & lastRow).Resize(12, 1).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("H47").Copy
    destWB.Sheets("Sieves").Range("L" & lastRow).Resize(12, 1).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("C53").Copy
    destWB.Sheets("Sieves").Range("M" & lastRow).Resize(12, 1).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("G48").Copy
    destWB.Sheets("Sieves").Range("N" & lastRow).Resize(12, 1).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("H48").Copy
    destWB.Sheets("Sieves").Range("O" & lastRow).Resize(12, 1).PasteSpecial xlPasteValues
   
'   Find last row of Samples data in desitnation workbook
    lastRow = destWB.Sheets("Samples").Cells(Rows.Count, "A").End(xlUp).Row + 1
   
'   Copy Samples data from source workbook to destination workbook
    srcWB.Sheets("A").Range("G20").Copy
    destWB.Sheets("Samples").Range("A" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("H20").Copy
    destWB.Sheets("Samples").Range("B" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("G3").Copy
    destWB.Sheets("Samples").Range("C" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("G5").Copy
    destWB.Sheets("Samples").Range("D" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("B6").Copy
    destWB.Sheets("Samples").Range("E" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Sheet2").Range("D31").Copy
    destWB.Sheets("Samples").Range("F" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Sheet2").Range("E31").Copy
    destWB.Sheets("Samples").Range("G" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Sheet2").Range("F31").Copy
    destWB.Sheets("Samples").Range("H" & lastRow).PasteSpecial xlPasteValues

'   Hide_Multiple_Sheets()
    destWB.Sheets("Samples").Visible = False
    destWB.Sheets("Sieves").Visible = False
   
'   Save changes and close destination workbook
    destWB.Close SaveChanges:=True

    Dim srcWB1 As Workbook
    Dim destWB1 As Workbook
    Dim fName1 As String
    Dim lastRows As Long
    Dim destName As String
    Dim wsName As String

'   Capture current workbook as source workbook
    Set srcWB = ActiveWorkbook

'   Set the name of the destination workbook
    destName = srcWB.Sheets("A").Range("F8").Text

'   Set the name of the destination worksheet
    wsName = srcWB.Sheets("A").Range("B6").Text

'   Open destination workbook and capture it as destination workbook
    Workbooks.Open "C:\Users\jdavis\Dropbox\Quality Control\Asphalt\Mold Heights\" & destName & ".xlsx"
    Set destWB = ActiveWorkbook
   
'   Error Route
    On Error GoTo ErrHandler:
    Worksheets(wsName).Activate

'   Find last row of data in desired worksheet of destination workbook
    lastRow = destWB.Sheets(wsName).Cells(Rows.Count, "A").End(xlUp).Row + 1

'   Copy Mold Heights data from source workbook to destination workbook
    srcWB.Sheets("A").Range("G3").Copy
    destWB.Sheets(wsName).Range("A" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("E46").Copy
    destWB.Sheets(wsName).Range("B" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("D22").Copy
    destWB.Sheets(wsName).Range("C" & lastRow).PasteSpecial xlPasteValues

'   Save changes and close destination workbook
    destWB.Close SaveChanges:=True

'   Export source workbook to PDF
    With srcWB
        fName = srcWB.Sheets("A").Range("A!F19").Value
        ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "C:\Users\jdavis\Dropbox\Quality Control\Asphalt\Asphalt Reports\" & fName, Quality:=xlQualityStandard, _
            includeDocProperties:=True, ignoreprintareas:=False, openafterpublish:=True
    End With
Exit Sub

ErrHandler:
    If Err.Number = 9 Then
        ' sheet does not exist, so create it
        Worksheets.Add.Name = wsName
        destWB.Sheets(wsName).Range("A1").Value = "Date"
        destWB.Sheets(wsName).Range("B1").Value = "Mold Height"
        destWB.Sheets(wsName).Range("C1").Value = "AC"
         destWB.Sheets(wsName).Range("F2").Value = "Avg Height"
          destWB.Sheets(wsName).Range("F3").Value = "Avg AC"
           destWB.Sheets(wsName).Range("G2").Value = "=Average(B:B)"
            destWB.Sheets(wsName).Range("G3").Value = "=Average(C:C)"
            destWB.Sheets(wsName).Range("A1", "A5000").NumberFormat = "mm/dd/yyyy"
            destWB.Sheets(wsName).Range("B1", "B5000").NumberFormat = "0.0"
            destWB.Sheets(wsName).Range("C1", "C5000").NumberFormat = "0.00"

'   Borders
             Range("F2:G3").Select
    Range("G3").Activate
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
        
        
ActiveSheet.ListObjects.Add(xlSrcRange, destWB.Sheets(wsName).Range("$A$1:$C$1"), , xlYes).Name = wsName
       
        '   Find last row of data in desired worksheet of destination workbook
    lastRow = destWB.Sheets(wsName).Cells(Rows.Count, "A").End(xlUp).Row

'   Copy Mold Heights data from source workbook to destination workbook
    srcWB.Sheets("A").Range("G3").Copy
    destWB.Sheets(wsName).Range("A" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("E46").Copy
    destWB.Sheets(wsName).Range("B" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("D22").Copy
    destWB.Sheets(wsName).Range("C" & lastRow).PasteSpecial xlPasteValues
   
'   Autofit Columns
    destWB.Sheets(wsName).Columns("A:G").AutoFit
   
'   Save changes and close destination workbook
    destWB.Close SaveChanges:=True

'   Export source workbook to PDF

    With srcWB
        fName = srcWB.Sheets("A").Range("A!F19").Value
       
       
           Sheets(Array("A", "Sheet2")).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "C:\Users\jdavis\Dropbox\Quality Control\Asphalt\Asphalt Reports\" & fName, _
        openafterpublish:=True, ignoreprintareas:=False



    End With
  
   
    srcWB.Sheets("Core Report").Visible = True
    srcWB.Sheets("Joints").Visible = True
       End If
  
End Sub
 
Upvote 0
The only reason I have the macro hide it at the beginning is the sheets array export to pdf for some reason is exporting every worksheet that's visible instead of the two that are named. There's only 4 visible worksheets in the book. "A","Sheet2","Core Report", "Joints". They're placed in that order. So I hid them at the start to not have that problem. Now they just won't be visible when the macro is done
 
Upvote 0
jondavis1987 said:
The only reason I have the macro hide it at the beginning is the sheets array export to pdf for some reason is exporting every worksheet that's visible instead of the two that are named.
Click to expand...

In that case, there's no need to hide your sheets. First, simply select the two sheets you want to print to PDF. For example, like this...

VBA Code: 
workbooks("Book1.xlsm").Sheets(array("A", "Sheet2")).select

Then, use the ExportAsFixedFormat method of the ActiveSheet, like this...

VBA Code: 
activesheet.ExportAsFixedFormat . . .

Note, I haven't had a chance to look at your code. So hopefully this helps.
 
Upvote 0
Okay, I took a quick look at your code. First, you have Exit Sub in the middle of your code. This means that only the code that occurs before this statement will get executed, unless there's an error. So, if there's no error, only ActiveWorkbook.ExportAsFixedFormat gets executed, which exports all sheets within the active workbook. Now, when there's an error, ActiveSheet.ExportAsFixedFormat gets executed. In this case, only the selected sheets gets exported. But it should written as follows...

VBA Code: 
    With srcWB
  
        .Activate 'needed in order to select the two sheets
      
        fName = .Sheets("A").Range("F19").Value 'instead of "A!F19"

        .Sheets(Array("A", "Sheet2")).Select
      
        .ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "C:\Users\jdavis\Dropbox\Quality Control\Asphalt\Asphalt Reports\" & fName, _
            openafterpublish:=True, ignoreprintareas:=False

    End With
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
VBA to create folder if it doesn't exist
Replies
2
Views
389
jondavis1987
J
M
  • Question
Document not Saved!
Replies
0
Views
231
MAlhash
M
K
  • Question
Excel Macro to Save to .pdf not working
Replies
9
Views
277
kpost
K
J
  • Solved
VBA Keeps Skipping a Paste
2
Replies
11
Views
727
jondavis1987
J
G
  • Solved
VBA to search & ignore missing WS' then convert to pdf
Replies
8
Views
306
GD03
G

Forum statistics

Threads
1,214,857
Messages
6,121,948
Members
449,056
Latest member
FreeCricketId

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top