VBA to Copy Specific Columns after Filter Has Been Applied

[kelster]

Hi,

Sheet one contains a table of table from C6:AY7000

On Sheet 2, I want to create a summary of data from Sheet 1 based on 2 Criteria which applies to column I and N.

On Sheet 1, I have put in a formula to link to sheet 2 for the criteria to make things easier.
So BN3 = Sheet2C3
And BN4= Sheet2C4

I have managed to run my coding to filter the data that I correctly want to copy and paste onto sheet 2 - however I only want to copy specific columns.

This is the coding I have written to select and filter the correct data values.

Sub AddFilter()
'
' AddFilter Macro
'
Dim rCrit1 As Range, rCrit2 As Range
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set rCrit1 = Range("BN3")
Set rCrit2 = Range("BN4")
Range("C6:AY1166").Select
Selection.AutoFilter
ActiveSheet.Range("$C$6:$AY$152").AutoFilter Field:=12, Criteria1:=rCrit1.Value
ActiveSheet.Range("$C$6:$AY$152").AutoFilter Field:=7, Criteria1:=rCrit2.Value

End Sub

This now gives me the correct data in the table.

I want to copy the visible cells from this table but only for columns e, dr, r, v, w, o, z, AD, AG, AQ, AW, AY

The copied data will then be pasted into sheet 2 starting in cells B9

I plan to attach a button on sheet 2, so when the user updates the values in C3 and C4, this will update values in BN3 and BN4 on sheet1, they can run my macro which will go to sheet 1, filter the data on what they have entered and paste the correct values.

Sorry if that sounds overlay complicated?!

Thanks for any help in advance

</SPAN>

<TBODY>
</TBODY>

 

[mole999]

you could use something like this

Sheet1.Range("e1:e200").Copy Destination:=Sheet2.Range("b9")
Sheet1.Range("dr1:dr200").Copy Destination:=Sheet2.Range("c9")
Sheet1.Range("r1:r200").Copy Destination:=Sheet2.Range("d9")

etc

that would be quick and easy to debug once you have one right

 

[kelster]

Perfect thank you very much! So simple when you see it!