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)


"ch-d" wrote in message
...
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!

"joel" wrote:


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("A1:D100").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/sh...d.php?t=170803

Microsoft Office Help

.