| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Want to Copy and paste to new sheet range except hidden rows.
Awesome,
Once again this group comes thru with the solution. Thanks much. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy/Paste with Hidden Rows/Columns Excel 2007 | Excel Discussion (Misc queries) | |||
| copy/paste with hidden rows don't want hidden parts to paste | Excel Discussion (Misc queries) | |||
| subtotal copy-paste and delete hidden rows | Excel Discussion (Misc queries) | |||
| copy a page with hidden rows and only paste what is showing | Excel Discussion (Misc queries) | |||
| copy and paste hidden rows | Excel Worksheet Functions | |||
Linear Mode
