Copy HIDDEN columns to a new workbook

[Rachel]

Hi there!

Is there any code which you can help me with to copy HIDDEN COLUMNS to a new
workbook.

I'm using below codes: (COLUMNS HIDDEN are columns J-K-L)

Sub Copy_With_AutoFilter1()
'Note: This macro use the function LastRow
Dim My_Range As Range
Dim CalcMode As Long
Dim ViewMode As Long
Dim FilterCriteria As String
Dim CCount As Long
Dim WSNew As Worksheet
Dim sheetName As String
Dim rng As Range
Dim WS As Worksheet

Set My_Range = Worksheets("Sheet1").Range("A1:BN" &
LastRow(Worksheets("Sheet1")))
My_Range.Parent.Select

If ActiveWorkbook.ProtectStructure = True Or _
My_Range.Parent.ProtectContents = True Then
ActiveWorkbook.Unprotect ("sda")
End If

ActiveSheet.Unprotect ("sda")

'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

'If you want to filter on a Inputbox value use this
FilterCriteria = InputBox("What text do you want to filter on?", _
"Enter the filter item.")
My_Range.autofilter Field:=4, Criteria1:="=" & FilterCriteria

'Check if there are not more then 8192 areas(limit of areas that Excel
can copy)
CCount = 0
On Error Resume Next
CCount =
My_Range.Columns(1).SpecialCells(xlCellTypeVisible).Areas(1).Cells.Count
On Error GoTo 0
If CCount = 0 Then
MsgBox "There are more than 8192 areas:" _
& vbNewLine & "It is not possible to copy the visible data." _
& vbNewLine & "Tip: Sort your data before you use this macro.", _
vbOKOnly, "Copy to worksheet"
Else

Set WSNew = Workbooks.Add(xlWBATWorksheet).Worksheets(1)

'Ask for the Worksheet name
sheetName = InputBox("What is the name of the new worksheet?", _
"Name the New Sheet")

On Error Resume Next
WSNew.Name = sheetName
If Err.Number > 0 Then
MsgBox "Change the name of sheet : " & WSNew.Name & _
" manually after the macro is ready. The sheet name" & _
" you fill in already exists or you use characters" & _
" that are not allowed in a sheet name."
Err.Clear
End If
On Error GoTo 0


'Copy/paste the visible data to the new worksheet
Selection.EntireColumn.Hidden = False
My_Range.Parent.autofilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
' Remove this line if you use Excel 97
.PasteSpecial Paste:=8
.PasteSpecial xlPasteAll
Application.CutCopyMode = False
.Select
ActiveSheet.Protection.AllowEditRanges.Add Title:="Range1",
Range:=Columns("AS:AX")
End With

Selection.autofilter
ActiveSheet.Protect ("sda")

End If

'Close AutoFilter
My_Range.Parent.AutoFilterMode = False

'Restore ScreenUpdating, Calculation, EnableEvents, ....
ActiveWindow.View = ViewMode
If Not WSNew Is Nothing Then WSNew.Select
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With

My_Range.Parent.Protect Password:="sda"

End Sub

 

[joel]

I kike most of your code. Here are some comments and the change you ar
asking for


1) It is beeter yo use Thisworkbook rather than Activeworkboook whe
refereing to the workbook with the macro. I also like to give th
worksheet a name as well as the range

From

Set My_Range = Worksheets("Sheet1").Range("A1:BN"
LastRow(Worksheets("Sheet1")))
My_Range.Parent.Select


ActiveWorkbook.Unprotect ("sda")

ActiveSheet.Unprotect ("sda")

to
Set SourceSht = Worksheets("Sheet1")

Set My_Range = SourceSht.Range("A1:BN" & LastRow(Sourcesht))
SourceSht.Select


ThisworkbookWorkbook.Unprotect ("sda")

Sourcesht.Unprotect ("sda")


2) When you do a copy the source type and the destination type must b
the same type object and you only have to specify the first item in th
destination and not the entire area. Just like copying manually on th
workbook.

What I mean is the following
Sheets("Sheet1").cells.copy destination:=Sheets("Sheet2").cells

with Sheets("sheet1")
.rows(1:5).copy destination:=.rows(100)
.columns("A:C").copy destination:=.columns("Z")
.Range("A1100").copy destination:=.range("T1")
end with


3) When using Autofilter use specialcells (visible) to copy your data.
You need to unhide the columns before copying

With Sourcesht
.Columns("J:L").hidden = False
Set Copyrange = .Columns("J:L").SpecialCells(xlCellTypeVisible)
Copyrange.Copy Destination:=.Columns("I:J"")
.Columns("J:L").hidden = True
CopyRange.Copy destination:=WSNew.columns("A")
End Wit

 

[ch-d]

Hi Joel,

Thank you for this. However, what I wanted to copy was the entire column
A:BN with columns J:L hidden in the sourcesheet but should be visible in the
new worksheet. Any help? THANKS!

 

[Rick Rothstein]

Sorry, but there was too much code in your original message to go through,
so I thought I would just provide an answer to what you posted in your last
message (the one I am replying to now) that you can use as a guide for
modifying your own code.

The following code will copy Columns A:BN from Sheet3 to Sheet5 and the
columns on Sheet5 will all be visible...

Worksheets("Sheet3").columns("A:BN").Copy Worksheets("Sheet5").Range("A1")
Worksheets("Sheet5").Columns("A:BN").Hidden = False

Note that using the Copy method (as I did in my first statement) copies not
only the cells contents, but any formatting that they have as well. Also
note that using this method, does not require selecting or pasting anything
nor do you have to shut off the "marching ants" (the copy selection border
indicator).

--
Rick (MVP - Excel)

Hi Joel,

Thank you for this. However, what I wanted to copy was the entire column
A:BN with columns J:L hidden in the sourcesheet but should be visible in
the
new worksheet. Any help? THANKS!

I kike most of your code. Here are some comments and the change you are
asking for


1) It is beeter yo use Thisworkbook rather than Activeworkboook when
refereing to the workbook with the macro. I also like to give the
worksheet a name as well as the range

From

Set My_Range = Worksheets("Sheet1").Range("A1:BN" &
LastRow(Worksheets("Sheet1")))
My_Range.Parent.Select


ActiveWorkbook.Unprotect ("sda")

ActiveSheet.Unprotect ("sda")

to
Set SourceSht = Worksheets("Sheet1")

Set My_Range = SourceSht.Range("A1:BN" & LastRow(Sourcesht))
SourceSht.Select


ThisworkbookWorkbook.Unprotect ("sda")

Sourcesht.Unprotect ("sda")


2) When you do a copy the source type and the destination type must be
the same type object and you only have to specify the first item in the
destination and not the entire area. Just like copying manually on the
workbook.

What I mean is the following
Sheets("Sheet1").cells.copy destination:=Sheets("Sheet2").cells

with Sheets("sheet1")
.rows(1:5).copy destination:=.rows(100)
.columns("A:C").copy destination:=.columns("Z")
.Range("A1100").copy destination:=.range("T1")
end with


3) When using Autofilter use specialcells (visible) to copy your data.
You need to unhide the columns before copying

With Sourcesht
.Columns("J:L").hidden = False
Set Copyrange = .Columns("J:L").SpecialCells(xlCellTypeVisible)
Copyrange.Copy Destination:=.Columns("I:J"")
.Columns("J:L").hidden = True
CopyRange.Copy destination:=WSNew.columns("A")
End With


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:
http://www.thecodecage.com/forumz/showthread.php?t=170803

Microsoft Office Help

.