Filtering and copying and pasting into another sheet

D

Donal28

Well-known Member
Joined
Apr 23, 2010
Messages
527
Hi All

I'm recording a macro to filter on columns 62 and 61 and then copy and pasting the results into a seperate sheet in a certain order(see below column letter ordering in brackets). The macro I have recorded works for column 62 = 150 and column 61 = EC but won't copy and paste new entries that have been added recently. do I have to do a sperate macro for each filtering( columns 62 & 61) combination? any help on this would be very much appreciated

Donal

<TABLE style="WIDTH: 562pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=749><COLGROUP><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" width=124><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3693" width=101><TBODY><TR style="HEIGHT: 30.75pt" height=41><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #eeece1; WIDTH: 63pt; HEIGHT: 30.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=41 width=84>Pro-Forma number(C:C)</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eeece1; WIDTH: 54pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=72>Job Card Reference(L:L)</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eeece1; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>Car No.(N:N)</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eeece1; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>Raft No.(M:M)</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eeece1; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>Raft Hours(AC:AC)</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eeece1; WIDTH: 60pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=80>Engine Serial no.(F:F)</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eeece1; WIDTH: 93pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=124>Date Work(AD:AD)</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eeece1; WIDTH: 72pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl68 width=96>Cost of works (Total inc Vat)(Z:Z)</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #eeece1; WIDTH: 76pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl66 width=101>Description of work(AK:AK)</TD></TR></TBODY></TABLE>


Recorded Macro Code

Sub Pop150EC()
'
' Populate5 Macro
'
'
Sheets("Cover page of LH works.").Select
ActiveSheet.Range("$A$3:$ADE$412").AutoFilter Field:=62, Criteria1:="150"
ActiveSheet.Range("$A$3:$ADE$412").AutoFilter Field:=61, Criteria1:="EC"
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 1
Range("C201:C399").Select
Selection.Copy
Sheets("150Engine Casualty Works").Select
Range("B21").Select
ActiveSheet.Paste
Sheets("Cover page of LH works.").Select
ActiveWindow.SmallScroll Down:=-15
Range("L201:L399").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("150Engine Casualty Works").Select
Range("C21").Select
ActiveSheet.Paste
Range("D21").Select
Sheets("Cover page of LH works.").Select
ActiveWindow.SmallScroll Down:=-12
Range("N201:N399").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("150Engine Casualty Works").Select
ActiveSheet.Paste
Range("E21").Select
Sheets("Cover page of LH works.").Select
ActiveWindow.SmallScroll Down:=-12
Range("M201:M399").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("150Engine Casualty Works").Select
ActiveSheet.Paste
Range("F21").Select
Sheets("Cover page of LH works.").Select
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.SmallScroll Down:=-12
Range("AC201:AC399").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("150Engine Casualty Works").Select
ActiveSheet.Paste
Sheets("Cover page of LH works.").Select
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=-12
Range("O201:O399").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("150Engine Casualty Works").Select
Range("G21").Select
ActiveSheet.Paste
Range("H21").Select
Sheets("Cover page of LH works.").Select
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll ToRight:=4
ActiveWindow.SmallScroll Down:=-15
Range("AD201:AD399").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("150Engine Casualty Works").Select
ActiveSheet.Paste
Range("I21").Select
Sheets("Cover page of LH works.").Select
ActiveWindow.SmallScroll Down:=-12
Range("Z201:Z399").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("150Engine Casualty Works").Select
ActiveSheet.Paste
Range("J21").Select
Sheets("Cover page of LH works.").Select
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.SmallScroll Down:=-12
Range("AK201:AK399").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("150Engine Casualty Works").Select
ActiveSheet.Paste
Range("A21").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "1"
Range("A22").Select
ActiveCell.FormulaR1C1 = "2"
Range("A23").Select
ActiveCell.FormulaR1C1 = "3"
Range("A24").Select
ActiveCell.FormulaR1C1 = "4"
Range("A25").Select
ActiveCell.FormulaR1C1 = "5"
Range("A26").Select
ActiveCell.FormulaR1C1 = "6"
Range("A27").Select
ActiveCell.FormulaR1C1 = "7"
Range("A28").Select
ActiveCell.FormulaR1C1 = "8"
Range("A29").Select
ActiveCell.FormulaR1C1 = "9"
Range("A30").Select
ActiveCell.FormulaR1C1 = "10"
Range("A31").Select
ActiveCell.FormulaR1C1 = "11"
Range("A32").Select
ActiveCell.FormulaR1C1 = "1"
Range("A32").Select
ActiveCell.FormulaR1C1 = "12"
Range("A33").Select
ActiveCell.FormulaR1C1 = "13"
Range("A34").Select
ActiveCell.FormulaR1C1 = "14"
Range("A35").Select
ActiveCell.FormulaR1C1 = "15"
Range("A36").Select
ActiveCell.FormulaR1C1 = "16"
Range("A37").Select
ActiveCell.FormulaR1C1 = "17"
Range("A38").Select
ActiveCell.FormulaR1C1 = "18"
Range("A39").Select
ActiveCell.FormulaR1C1 = "19"
Range("A40").Select
ActiveCell.FormulaR1C1 = "20"
Range("A41").Select
ActiveCell.FormulaR1C1 = "21"
Range("A42").Select
ActiveCell.FormulaR1C1 = "22"
Range("A43").Select
ActiveCell.FormulaR1C1 = "23"
Range("A44").Select
ActiveCell.FormulaR1C1 = "24"
Range("A45").Select
ActiveCell.FormulaR1C1 = "25"
Range("A46").Select
ActiveCell.FormulaR1C1 = "26"
Range("A47").Select
ActiveCell.FormulaR1C1 = "27"
Range("A48").Select
ActiveCell.FormulaR1C1 = "28"
Range("A49").Select
ActiveCell.FormulaR1C1 = "29"
Range("A50").Select
ActiveCell.FormulaR1C1 = "30"
Range("A51").Select
ActiveCell.FormulaR1C1 = "31"
Range("A52").Select
ActiveCell.FormulaR1C1 = "32"
Range("A53").Select
ActiveCell.FormulaR1C1 = "33"
Range("A54").Select
ActiveCell.FormulaR1C1 = "34"
Range("A55").Select
ActiveCell.FormulaR1C1 = "35"
Range("A56").Select
ActiveCell.FormulaR1C1 = "36"
Range("A57").Select
ActiveCell.FormulaR1C1 = "37"
Range("A58").Select
ActiveCell.FormulaR1C1 = "38"
Range("A59").Select
ActiveCell.FormulaR1C1 = "39"
Range("A60").Select
ActiveCell.FormulaR1C1 = "40"
Range("A61").Select
ActiveCell.FormulaR1C1 = "41"
Range("A62").Select
ActiveCell.FormulaR1C1 = "42"
Range("A63").Select
Sheets("Cover page of LH works.").Select
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 60
ActiveWindow.ScrollColumn = 61
ActiveWindow.ScrollColumn = 63
ActiveWindow.ScrollColumn = 65
ActiveWindow.ScrollColumn = 66
ActiveWindow.ScrollColumn = 68
ActiveWindow.ScrollColumn = 70
ActiveWindow.ScrollColumn = 72
ActiveWindow.ScrollColumn = 70
ActiveWindow.ScrollColumn = 68
ActiveWindow.ScrollColumn = 66
ActiveWindow.ScrollColumn = 65
ActiveWindow.ScrollColumn = 63
ActiveWindow.ScrollColumn = 61
ActiveWindow.ScrollColumn = 60
ActiveWindow.ScrollColumn = 58
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollRow = 206
ActiveWindow.ScrollRow = 204
ActiveWindow.ScrollRow = 202
ActiveWindow.ScrollRow = 201
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 1
ActiveSheet.Range("$A$3:$ADE$412").AutoFilter Field:=61
ActiveSheet.Range("$A$3:$ADE$412").AutoFilter Field:=62
End Sub
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
You must log in or register to reply here.

Similar threads

J
  • Question Question
Issue with Macros and renaming files
Replies
6
Views
1K
kevin9999
K
T
  • Question Question
ActiveSheet. Paste error
Replies
1
Views
351
RoryA
RoryA
P
  • Question Question
Help with Vba Code
Replies
3
Views
476
Coyotex3
C
D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
624
smozgur
smozgur
M
  • Question Question
Loop the Macro to next row until end of sheet
Replies
2
Views
697
rollis13
rollis13

Forum statistics

Threads
1,224,609
Messages
6,179,873
Members
452,949
Latest member
Dupuhini

Share this page

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
Back
Top