VBA copying data to temp worksheet

RCGintact

New Member
Joined
Feb 20, 2012
Messages
2
Hi, I'm new to VBA but I've found this macro on a previous thread that does exactly what I'm looking for, except that I need it to ignore hidden tabs in my workbook.

The macro basically copies the content of my worksheets, creates a temps sheet, and the data gets pasted on it so users can print it as one single document.

Can somebody help me find where to put the line "If wsh.Visible = xlSheetVisible Then..." (or something like it)?

Here's the code:
Sub PrintOnePage()
Dim wshTemp As Worksheet, wsh As Worksheet
Dim rngArr() As Range, c As Range
Dim i As Integer
Dim j As Integer

ReDim rngArr(1 To 1)
For Each wsh In ActiveWorkbook.Worksheets
i = i + 1
If i > 1 Then ' resize array
ReDim Preserve rngArr(1 To i)
End If

On Error Resume Next
Set c = wsh.Cells.SpecialCells(xlCellTypeLastCell)
If Err = 0 Then
On Error GoTo 0

'Prevent empty rows
Do While Application.CountA(c.EntireRow) = 0 _
And c.EntireRow.Row > 1
Set c = c.Offset(-1, 0)
Loop

Set rngArr(i) = wsh.Range(wsh.Range("A1"), c)
End If
Next wsh

'Add temp.Worksheet
Set wshTemp = Sheets.Add(after:=Worksheets(Worksheets.Count))

On Error Resume Next
With wshTemp
For i = 1 To UBound(rngArr)
If i = 1 Then
Set c = .Range("A1")
Else
Set c = _
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)
Set c = c.Offset(2, 0).End(xlToLeft) ' skip one row
End If

'Copy-paste range (prevent empty range)
If Application.CountA(rngArr(i)) > 0 Then
rngArr(i).Copy c
End If
Next i
End With
On Error GoTo 0

Application.CutCopyMode = False ' prevent marquies

' Filter rows marked X
With ActiveSheet
.AutoFilterMode = False
With Range("e1", Range("e" & Rows.Count).End(xlUp))
.AutoFilter 1, "* *"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With

'Print Desired Number of Copies
i = InputBox("Nombre de copies?", "ExcelTips", 1)
If IsNumeric(i) Then
If i > 0 Then
ActiveSheet.PrintOut Copies:=i
End If
End If

'Delete temp.Worksheet
Application.DisplayAlerts = False
wshTemp.Delete
Application.DisplayAlerts = True

End Sub
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Event Horizon

New Member
Joined
Feb 20, 2012
Messages
4
For Each wsh In ActiveWorkbook.Worksheets

'Only add to array if sheet is visible
If wsh.Visible = xlSheetVisible Then '<<< Add this here

i = i + 1
If i > 1 Then ' resize array
ReDim Preserve rngArr(1 To i)
End If

On Error Resume Next
Set c = wsh.Cells.SpecialCells(xlCellTypeLastCell)
If Err = 0 Then
On Error GoTo 0

'Prevent empty rows
Do While Application.CountA(c.EntireRow) = 0 _
And c.EntireRow.Row > 1
Set c = c.Offset(-1, 0)
Loop

Set rngArr(i) = wsh.Range(wsh.Range("A1"), c)
End If

End If '<<< Add this here

Next wsh
 

Watch MrExcel Video

Forum statistics

Threads
1,132,686
Messages
5,654,754
Members
418,151
Latest member
shukoor

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
Top