copy data from a hidden column to another sheet



I have used code from Ron De Bruin which will copy entire rows that meet a
specific criteria to another worksheet.
This works really well unless there are hidden columns in the source sheet.
How can I unhide column E (in my case) before the copying part of the code

Here's the beginning of the code:

Sub Copy_Complete()

Dim My_Range As Range
Dim DestSh As Worksheet
Dim CalcMode As Long
Dim ViewMode As Long
Dim FilterCriteria As String
Dim CCount As Long
Dim rng As Range
Dim My_Range1 As Range

Set My_Range = Range("D3:BD" & LastRow(ActiveSheet))

'Set the destination worksheet
Set DestSh = Sheets("complete")

'Change ScreenUpdating, Calculation, EnableEvents, ....
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
ActiveSheet.DisplayPageBreaks = False

'Firstly, remove the AutoFilter
My_Range.Parent.AutoFilterMode = False

'Filter and set the filter field and the filter criteria

My_Range.AutoFilter Field:=47, Criteria1:="=P"

And off it goes!

Can I insert a line or two which will:
unhide column E
then continue with the copying over
re-hide column E




My_Range.Worksheet.Columns(5).Hidden = False

to reveal and:

My_Range.Worksheet.Columns(5).Hidden = True

to hdie again.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question