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]

Not for me it doesn't, do you have any merged cells?
That said, this would be better as it may be calculating the last row incorrectly.

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
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .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]

No. there are no merge cells

 

[Fluff]

In that case I don't know what to suggest, it should only copy the data from row 2 downwards, regardless of whether there are any visible cells.

 

[vmjan02]

yes agreed. as I am also very confused.
As did tried every possible code. but it still copies the header.

 

[Fluff]

Can you share your workbook? If so you will need to upload to a share site, such as OneDrive or Google, mark for sharing & then post a link to this thread.

 

[vmjan02]

sure.

in the mean while. I have worked on this solution. Currently it has solved the problem

On Error Resume Next
Sheets("Raw Data 2 Hrs").Select
With ActiveSheet
    .AutoFilterMode = False
    .Range("A1:BK" & LastrowC).AutoFilter Field:=1, Criteria1:="S.No."
    .Range("a1:bk" & LastrowC).Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete ''delete extra header if any
    .AutoFilterMode = False
End With
Sheets("Raw Data 2 Hrs").Visible = False

will share the file.