Want to Copy and paste to new sheet range except hidden rows.
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("A1:D23").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? |
Want to Copy and paste to new sheet range except hidden rows.
Try this
Worksheets.Add ThisWorkbook.ActiveSheet.Name = "Report" ThisWorkbook.Sheets("WorkingSheet").Select Range("A1:D23").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 " wrote: 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("A1:D23").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? |
Want to Copy and paste to new sheet range except hidden rows.
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 " wrote: 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("A1:D23").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? |
Want to Copy and paste to new sheet range except hidden rows.
another way perhaps?
With ThisWorkbook .Worksheets.Add .ActiveSheet.Name = "Report" .Worksheets("WorkingSheet").Range("A1:D23") _ .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 -- jb " wrote: 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("A1:D23").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? |
Want to Copy and paste to new sheet range except hidden rows.
Awesome,
Once again this group comes thru with the solution. Thanks much. |
All times are GMT +1. The time now is 01:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com