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