PDA

View Full Version : [SOLVED] copy paste after the lastused row of another sheet excluding the headers



Tharabai
09-12-2015, 01:50 PM
Hi,

I used autofilter and copy pasted the visible cells to a another sheet named "result".


Again I used autofilter with different criteria and copy paste the visible cells after the last used row of sheet "result".
I have used to below code but this includes Header as well. But I want to exclude the header as I am pasting the values after the last used cell.



Range("A2:dG2").Select
Dim rng As Range
Set rng = Application.Intersect(ActiveSheet.UsedRange, Range("A:dG"))
rng.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Dim lastrow As Long
Sheets("result").Select
lastrow = Sheets("result").Range("A1048576").End(xlUp).Row
Sheets("result").Activate
Cells(lastrow + 1, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False



-Tharabai

mancubus
09-12-2015, 02:24 PM
try this. adopt filter colums, crits, sheet names to your workbook.



Sub vbax_53726_CopyAutoFilterRangeWHeadersWOHeaders()

With Application
.DisplayAlerts = False
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With

With Worksheets("Sheet1") 'change Sheet1 to suit
.AutoFilterMode = False 'remove existing filters, if any

.Cells(1).AutoFilter Field:=1, Criteria1:="=MyCrit1"
.AutoFilter.Range.Copy 'copy with headers
Worksheets("result").Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
.AutoFilterMode = False

.Cells(1).AutoFilter Field:=3, Criteria1:="=MyCrit2"
.UsedRange.Columns(1).Offset(1).SpecialCells(12).EntireRow.Copy 'copy without headers
Worksheets("result").Range("A1048576").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
.AutoFilterMode = False

.Cells(1).AutoFilter Field:=5, Criteria1:="=MyCrit3"
.UsedRange.Columns(1).Offset(1).SpecialCells(12).EntireRow.Copy 'copy without headers
Worksheets("result").Range("A1048576").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
.AutoFilterMode = False
End With

With Application
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With

End Sub

Tharabai
09-12-2015, 02:57 PM
Perfect !!!!

Thank you so much for your quick assistance...

-Tharabai