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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,265
Office Version
  1. 365
Platform
  1. Windows
How about
Rich (BB code):
Z.Range("a1:bk" & LR).Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy A.Range("a1" & L1 + 1)
 

vmjan02

Well-known Member
Joined
Aug 15, 2012
Messages
692
Office Version
  1. 2013
This is the new code and it is still copy pasting the header. Just not able to exclude the copy past of header

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("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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,265
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Is the header in row 1?
 

vmjan02

Well-known Member
Joined
Aug 15, 2012
Messages
692
Office Version
  1. 2013
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,265
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

vmjan02

Well-known Member
Joined
Aug 15, 2012
Messages
692
Office Version
  1. 2013
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

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

Well-known Member
Joined
Aug 15, 2012
Messages
692
Office Version
  1. 2013
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.
 

Forum statistics

Threads
1,141,130
Messages
5,704,454
Members
421,350
Latest member
jake9951

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