Sub
CopySpcial()
Dim
wshSource
As
Worksheet
Dim
wshDestination
As
Worksheet
Dim
rngCopy
As
Range
Dim
iColEnd
As
Integer
, iColStart
As
Integer
Set
wshSource = ActiveSheet
Set
wshDestination = ActiveWorkbook.Worksheets.Add(After:=wshSource)
Set
rngCopy = wshSource.Range(
"B1:B10"
)
rngCopy.Copy
wshDestination.Range(
"B1"
).PasteSpecial xlPasteAll
iColEnd = wshSource.UsedRange.SpecialCells(xlCellTypeLastCell).Column
iColStart = IIf(iColEnd > 11, iColEnd - 11, 2)
Set
rngCopy = wshSource.Range(wshSource.Cells(1, iColStart), wshSource.Cells(10, iColEnd))
rngCopy.Copy
wshDestination.Cells(1, iColStart).PasteSpecial xlPasteAll
Application.CutCopyMode =
False
End
Sub