Want to Copy and paste to new sheet range except hidden rows.

[HammerJoe]

Hi,

I have a macro that copies on sheet to another and I use this to
achieve it:

Worksheets.Add
ThisWorkbook.ActiveSheet.Name = "Report"
ThisWorkbook.Sheets("WorkingSheet").Select
Range("A123").Select
'Application.CutCopyMode = False
Selection.Copy

Sheets("Report").Activate
Sheets("Report").Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

The problem is that there are some Rows within the range that are
hidden and dont want to copy them to the new sheet or at least keep it
hidden as well.

Is there a way with copy/paste to ignore hidden rows or have to use
VBA to hide the rows in the new sheet as well?

 

[Mike H]

Try this

Worksheets.Add
ThisWorkbook.ActiveSheet.Name = "Report"
ThisWorkbook.Sheets("WorkingSheet").Select
Range("A123").Select
'Application.CutCopyMode = False

' selection.copy
Selection.SpecialCells(xlCellTypeVisible).Copy

Sheets("Report").Activate
Sheets("Report").Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False



Mike

 

[Karthi Ganesh]

Try this code, change the ranges as per your requirement

Range("B3:B8").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Sheet2").Select
Range("I6").Select
ActiveSheet.Paste
Range("K9").Select

regards
KarthiGanesh

 

[john]

another way perhaps?


With ThisWorkbook
.Worksheets.Add
.ActiveSheet.Name = "Report"
.Worksheets("WorkingSheet").Range("A123") _
.SpecialCells(xlCellTypeVisible).Copy
With .Worksheets("Report").Range("A1")
.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With
End With

 

[HammerJoe]

Awesome,

Once again this group comes thru with the solution.

Thanks much.