0

Firstly I have to apologise as this is my first attempt at any VBA coding!

I am trying to find a way of automating a process whereby I extract data from a single data sheet with multiple columns in it into several separate worksheets.

My source data sheet has multiple column headings that are identified by their heading name, column number and column reference.

I am looking for some code that will (for the 2 specified column headings in each of my output sheets:

  • perform an auto filter selection (=Blanks for column 1 in the operation, <> Blanks for column 2 in the operation)
  • copy and paste the returned data into a specified output sheet
  • reset the auto filters in data sheet
  • move onto the next output sheet, select the next two relevant columns identified in the next output sheet
  • perform an auto filter selection (=Blanks for column 1 in the operation, <> Blanks for column 2 in the operation)
  • copy and paste the returned data into a specified output sheet
  • reset the auto filters in data sheet etc.

I can do this for 1 sheet and repeat but fall over due to the data sheet columns not necessarily remaining in the same order (as new activity milestones are added between the activity I am counting are added).

My crude code is below:

Sub FcastSFR()
'
' FcastSFR Macro
'

'
    Application.ScreenUpdating = False

    
    Range("A23:B345").Select
    Selection.ClearContents
    Sheets("MOAT").Select
    ActiveSheet.ShowAllData
    ActiveSheet.Range("$A$1:$DT$5000").AutoFilter Field:=17, Criteria1:="="
    ActiveSheet.Range("$A$1:$DT$5000").AutoFilter Field:=15, Criteria1:="<>"
    Range("O1:P631").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("SFR Submit").Select
    Range("A23").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

I am looking for help to make the Field=17/Field=15 and the Range ("O1:P631") be selected from the relevant output sheets.

Help!!

BigBen
  • 46,229
  • 7
  • 24
  • 40
user1771852
  • 1
  • 2
    Use `Application.Match` or `Range.Find` to figure out what column the headings in question are in. – BigBen Jul 25 '20 at 17:22
  • As @BigBen suggests - [Find](https://learn.microsoft.com/en-us/office/vba/api/excel.range.find) is a good starting point. If you follow the example in the link change the `Loop While` line to `Loop While c.Address <> firstAddress`. – Darren Bartrup-Cook Jul 25 '20 at 21:31
  • Also strongly suggest you have a read of [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?r=SearchResults) post and get out of the habit of using `Select` & `Active` as quickly as possible. – Darren Bartrup-Cook Jul 25 '20 at 21:36

0 Answers0