AutoFilter CopyPast

[vmjan02]

the Code works all fine but the only problem is it is not pasting the data one below the other any help

Sub AutoFilterData()
Sheets("MSP Response Time RawData").Select
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With ActiveSheet
    .AutoFilterMode = False
    .Range("A1:BK" & LR).AutoFilter Field:=54, Criteria1:="1"
    .Range("A1:BK" & LR).AutoFilter Field:=63, Criteria1:="2 Hrs"
End With

Dim A As Worksheet
Dim Z As Worksheet
Dim L1 As Long
Set A = Sheets("Raw Data 2 Hrs")
Set Z = Sheets("MSP Response Time RawData")
L1 = A.Cells(Rows.Count, "A").End(xlUp).Row  'here not selectiong the last empty row
Z.Range("a1:bk" & LR).Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy A.Range("a1" & L1)  'here not copying the last empty row

Sheets("MSP Response Time RawData").Select
With ActiveSheet
    .AutoFilterMode = False
    .Range("A1:BK" & LR).AutoFilter Field:=54, Criteria1:="1"
    .Range("A1:BK" & LR).AutoFilter Field:=63, Criteria1:="4 Hrs"
End With
Dim B As Worksheet
Dim Z1 As Worksheet
Dim L2 As Long
Set B = Sheets("Raw Data 4 Hrs")
Set Z1 = Sheets("MSP Response Time RawData")
L2 = B.Cells(Rows.Count, "A").End(xlUp).Row 'here not selectiong the last empty row
Z1.Range("a1:bk" & LR).Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy B.Range("A1" & L2)   'here not copying the last empty row
end sub

 

[Fluff]

How about

Z.Range("a1:bk" & LR).Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy A.Range("a1" & L1 + 1)

 

[vmjan02]

A.Range("a1" & L1 + 1)

sorry for my late response. the code is not working

 

[vmjan02]

This is the new code and it is still copy pasting the header. Just not able to exclude the copy past of header

Sub AutoFilterData()

''===================================================================
'This code will copy paste required Raw data in the respective Data Sheets
'sheets:-  RawData 2 hrs, Raw Data 4 Hrs amd so on
''===================================================================
Sheets("Final Data").Visible = True
Sheets("Final Data").Select
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With ActiveSheet
    .AutoFilterMode = False
    .Range("A1:BK" & LR).AutoFilter Field:=54, Criteria1:="1"
    .Range("A1:BK" & LR).AutoFilter Field:=63, Criteria1:="2 Hrs"
End With

Sheets("Raw Data 2 Hrs").Visible = True
Dim LastrowC1 As Long
LastrowC1 = Sheets("Final Data").Cells(Rows.Count, "A").End(xlUp).Row
Dim LastrowC As Long
LastrowC = Sheets("Raw Data 2 Hrs").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Final Data").Range("A2:BK" & LastrowC1).Copy Sheets("Raw Data 2 Hrs").Cells(LastrowC, 1)
LastrowC = Sheets("Raw Data 2 Hrs").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Raw Data 2 Hrs").Visible = False

 

[Fluff]

Is the header in row 1?

 

[vmjan02]

yes. correct

many times it may happen that there will not be an records.
In this case my code is copy pasting the header.

 

[Fluff]

In that case the header should not get copied, unless there are no visible cells.

 

[vmjan02]

true

I tried to this as well

Sheets("Final Data").Range("A2:BK" & LastrowC1).SpecialCells(xlCellTypeVisible).Copy Sheets("Raw Data 2 Hrs").Cells(LastrowC, 1)

but still the same it did copied the header

any suggestion or modification.

 

[Fluff]

How about

Sub AutoFilterData()

''===================================================================
'This code will copy paste required Raw data in the respective Data Sheets
'sheets:-  RawData 2 hrs, Raw Data 4 Hrs amd so on
''===================================================================
Sheets("Final Data").Visible = True
Sheets("Raw Data 2 Hrs").Visible = True
Dim LastrowC As Long
LastrowC = Sheets("Raw Data 2 Hrs").Cells(Rows.Count, "A").End(xlUp).Row + 1

Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With Sheets("Final Data")
    .AutoFilterMode = False
    .Range("A1:BK" & LR).AutoFilter Field:=54, Criteria1:="1"
    .Range("A1:BK" & LR).AutoFilter Field:=63, Criteria1:="2 Hrs"
    .AutoFilter.Range.Offset(1).Copy Sheets("Raw Data 2 Hrs").Cells(LastrowC, 1)
End With
Sheets("Raw Data 2 Hrs").Visible = False

 

[vmjan02]

Sheets("Final Data").Visible = True Sheets("Raw Data 2 Hrs").Visible = True Dim LastrowC As Long LastrowC = Sheets("Raw Data 2 Hrs").Cells(Rows.Count, "A").End(xlUp).Row + 1 Dim LR As Long LR = Range("A" & Rows.Count).End(xlUp).Row With Sheets("Final Data") .AutoFilterMode = False .Range("A1:BK" & LR).AutoFilter Field:=54, Criteria1:="1" .Range("A1:BK" & LR).AutoFilter Field:=63, Criteria1:="2 Hrs" .AutoFilter.Range.Offset(1).Copy Sheets("Raw Data 2 Hrs").Cells(LastrowC, 1) End With Sheets("Raw Data 2 Hrs").Visible = False

Hi buddy.

Hard luck. its still taking the header.