AutoFilter CopyPast

vmjan02

Well-known Member
Joined
Aug 15, 2012
Messages
692
Office Version
  1. 2013
the Code works all fine but the only problem is it is not pasting the data one below the other any help

VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,239
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,239
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Aug 15, 2012
Messages
692
Office Version
  1. 2013
yes agreed. as I am also very confused.
As did tried every possible code. but it still copies the header.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,239
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Aug 15, 2012
Messages
692
Office Version
  1. 2013
sure.

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

VBA Code:
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.
 

Forum statistics

Threads
1,141,049
Messages
5,703,942
Members
421,321
Latest member
blusky4

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top