0

Looking for a solution to probably a very simple problem but somehow I can not seem to find an answer.

I am looking for a way to copy the first cell of each column. I want to use the copied value to filter it in another workbook. I want to paste the filtered cells below and then repeat the process until all columns have been gone through.

Thus what kind of loop would I use to copy every first cell at a time, store it, then do something with it and then copy the next first cell of a column for all columns?

Could someone please me with this? Your help is much appreciated.

For i = 1 To 207
    Columns(i).Select
    ActiveCell.Offset(0, 0).Range("A1").Select
    Selection.Copy
'How do I store the copied value to use it in an autofilter?


Next i

End Sub
Fastbanana
  • 1
  • 1

1 Answers1

0

Instead of selecting – and you should avoid using select –
You can set the value to a variable varname = Cells(1,i) for later use,
or better yet, apply directly to your filter.

Dim i As Long
Dim wb2 As Workbook
Set wb2 = Workbooks("Book2") 'the "Other" workbook name here

For i = 1 To wb2.Worksheets.Count 'looping each sheet
    wb2.Worksheets(i).Range("A1").AutoFilter Field:=1, Criteria1:= _
    ThisWorkbook.Sheets(1).Cells(1, i)
Next i

This is just an example which makes a lot of assumptions that more than likely does not fit with your reality, these has to be changed to fit your scenario, ofc.
But it would go through the specified amount of worksheets in the specified workbook, and filter the first column with the value obtained from the first workbook.

Then, there are quite a few questions on how to copy a filtered range that might give a hint.

Christofer Weber
  • 1,464
  • 1
  • 9
  • 18