Can't copy hidden columns

[mharper90]

All of my research seems to agree that I should be able to copy the data from hidden columns, but I just can't get it to work. I've tried this exact macro with and without columns E and F being hidden, and it'll only give me the desired result if E and F are not hidden.

Background: ws1 is a roster of all employees at my job and a lot of data pertaining to each. ws2 is a sub-roster of only the employees that fall into a certain category (ws1, column A), and only the data that matters. This macro should find those who meet the ws1, column A criteria to be included on ws2, and populate ws2 with the desired subset of data. Columns E and F are hidden on ws1, but the data is important on ws2.

*Disclaimer, this code is retyped from a printout because the internet does not meet the classification requirements of the network this file exists on. Please excuse minor typos possibly from my retyping error, but point them out if you think they may be the problem for this code not working.

Sub cpypste2()

Dim x As String
Dim ws1 As Worksheet: Set ws1 = Thisworkbook.Sheets("Main Data")
Dim ws2 As Worksheet: Set ws2 = Thisworkbook.Sheets("Figure")
Dim r1 As Range, r2 As Range, r3 As Range, r4 As Range, multiAreaRange As Range​

ws1.Activate​

Set r1 = Range("B:C")
Set r2 = Range("E:F") 'these columns are hidden on ws1
Set r3 = Range("H:H")
Set r4 = Range("N:N")
Set multiAreaRange = Union(r1, r2, r3, r4)
​

​

Application.ScreenUpdating = False

x = "NO"

If ws2.Range("A" & Rows.Count).End(xlUp).Row > 2 Then​

ws2.UsedRange.Offset(2).Clear​

If Not IsError(Application.Match(x, ws1.Range("A:A"), 0)) Then
​

ws1.Range("A3").CurrentRegion.AutoFilter Field:=1, Criteria1:=x
Intersect(ws1.AutoFilter.Range.Offset(1), multiAreaRange).Copy _​

Destination:=ws2.Range("A" & Rows.Count).End(xlUp).Offset(1)​

ws1.AutoFilterMode = False​

End If​

SortGroup2Printout
Application.ScreenUpdating = True
End Sub

​

 

[Yongle]

Try

Insert after ws1.Activate

 ws1.Columns("E:F").Hidden = False

Insert after End If

 ws1.Columns("E:F").Hidden = True

 

[mharper90]

Thanks! I'll give that a try as soon as I get back to the office and will let you know.

 

[mharper90]

Try

Insert after ws1.Activate

 ws1.Columns("E:F").Hidden = False

Insert after End If

 ws1.Columns("E:F").Hidden = True

Thanks! That worked!

 

[Yongle]

Thanks for the feedback