VBA Copy and Paste Columns Out of Order

[lpw0806]

Hi -

I am filtering on values on a Raw Data tab and want to Copy and Paste the values into a different sheet (75% - 95% Opps). I am so close to getting it right.....expect I want to paste the values from the Raw Data tab in a different column / order on the new sheet. Right now its just pasting them in the order they are in the Raw Data tab

Also, is there a way to do this without ranges? Since the number of rows will change every time?

Thank you!!

Sub Opps_Btwn_75_95()
'
' Opps_Btwn_75_95 Macro


' Clear Data from Sheet
Sheets("75% - 95% Opps").Range("A2:G200").Clear


' Filter Seller POD
Dim ary As Variant
Dim i As Long
ary = Array("Auto", "Multi", "Tech", "Lifestyle")

For i = 0 To UBound(ary)
With Sheets("Raw Data")
If .AutoFilterMode Then .AutoFilterMode = False
With .Range("A1:W1")
.AutoFilter 11, ">=" & 75
.AutoFilter 11, "<" & 100
.AutoFilter 19, ary(i)
End With

'select these columns and paste them into the 75% - 95% Opps sheet in this order
Intersect(.AutoFilter.Range, .Range("C2:C10000,B2:B10000,Q2:Q10000,K2:K10000,R2:R10000,A2:A10000,S2:S10000")).Copy Sheets("75% - 95% Opps").Range("A2")
.AutoFilterMode = False
End With
Next i


End Sub

 

[Rick Rothstein]

I think this macro will do what you want (without needing to do any filtering nor any looping)...

[table="width: 500"]
[tr]
	[td]Sub CopyCertainColumns()
  Dim LastRow As Long, Data As Variant
  LastRow = Sheets("Raw Data").Cells.Find("*", , xlValues, , xlRows, xlPrevious, , , False).Row
  Data = Application.Index(Sheets("Raw Data").Cells, Evaluate("ROW(2:" & LastRow & ")"), [{3,2,17,11,18,1,19}])
  Sheets("75% - 95% Opps").Range("A2").Resize(UBound(Data, 1), UBound(Data, 2)) = Data
End Sub[/td]
[/tr]
[/table]

 

[lpw0806]

Hi - Thanks so much for your response. I ran into a few issues with the code you provided

1) It didn't clear the data in range A:G (have headers)
2) It didn't filter for "Auto", "Multi", "Tech", "Lifestyle" in the Array - it included all of the variables
3) It cleared any formatting I had in the output tab

Any idea on how to adjust to include these three things?

Thanks again in advance!