VBA Code Copy&Paste Request

Hello all,

I have approximately 35 files that I need some help.

I’m attaching the following file named “450 FY 2021 Expenses-SSE-Budget”

On it, I have four (4) tabs:

They are:

• FY 21-Budget-Expenses-Option2
• 7-5 Reforecast
• 7-5 Reforecast Sample---this is the tab showing the final result after copying the information.
• Accounts

If you go to tab “FY 21-Budget-Expenses-Option2” and go to cell C14 you will see the following info:
56220 Credit Card Fees-All Others

If you look at cell C20 you will see the same value: 56220 Credit Card Fees-All Others

With a macro, I would like to copy all the information within this account and other accounts:
“56220 Credit Card Fees-All Others”

The specific information in this case is between cell C15 and C19

In any case, if there is no information between these two cells (C15 and C19), then the macro should exclude or skips the blank items)

Therefore, if there is information in any cell in Column C depending on this value mentioned above, it should copy the entire row to the “7-5 Reforecast” tab, where the description should go to cell B125 and down, and the amounts from cells H thru S in the tab “FY 21-Budget-Expenses-Option2” should be copy to Column C thru Column N in tab “7-5 Reforecast”.

To finalize, it will a nice addition if I can run the macro for different accounts at the same time.

For example,

56220 Credit Card Fees-All Others

56500 Bank Service Charges

57700 Office & Computer Supplies

59990 Other

If possible, if the account number could also be copy with the macro to Column A in the tab “7-5 Reforecast”, that will be perfect.

As always, thank you for your support.

rsrasc

What have you tried? After all, you've made well over 100 posts to date in various forums, treating them like free coding services.

@Macropod

Thanks for your attentiveness :thumb and warning us :clap:

Just create a table from each autofilter target value, you set it here.

Code:

---

Private Sub cmdFilerCriteriaToNewXLTable_Click()
Dim Excel As Object
Dim excelSheet As Object
Dim excelSheet2 As Object
Dim Range As Object


    Set Excel = GetObject(, "Excel.Application")
    Set excelSheet = Excel.ActiveWorkbook.Sheets("YOUR_TABLE")
   
    Excel.Visible = True
   


    'from an autofiltered column on this table, can be made "Sheet1", etc.
   
    Excel.ActiveWorkbook.Sheets("YOUR_TABLE").Select


    strFILT = YOUR_TARGET_FILTER 'like 9999.99
   
    'be on sheet "YOUR_TABLE"
    'can delete this if autofilter is already on
    Excel.application.Range("A1:Z1").Select
    'Excel.application.Range("YOUR_RANGE_ONECOLUMN").Select
    '============
    'added this to set autofilter field val automaically
    strRange = "YOUR_RANGE_ONECOLUMN"
    strFC = Excel.application.Range(strRange).Column
   
    'SET FIELD TO AUTOFILTER COLUMN (EXPLICIT=10) OR NO DATA RETURNED WITH NO ERROR
    'if problem make field explicit to column loc rather than strFC var
    'Excel.application.Selection.AutoFilter Field:=strFC, Criteria1:=strFILT
   
    Excel.ActiveWorkbook.Sheets("Table_Complete").Select
    Excel.application.Selection.AutoFilter Field:=10, Criteria1:=strFILT
   
    'GETS ALL CELLS WITH DATA
    excelSheet.Range("A1", excelSheet.Range("A1").End(xlToRight).End(xlDown)).Cells(1, 1).Select
   
    With Excel.application.Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
       
    End With
 
    'NAME RANGE
    STRPLANTFILTER = Replace(strFILT, " ", "_") 'YOU MAY HAVE TO
    'PREFIX YOUR CRITERIA TO AN ALLOWABLE XL TABLE NAME
   
    excelSheet.Range("A1", excelSheet.Range("A1").End(xlToRight).End(xlDown)).Select
   
    'COPIES THE AUTOFILTERED SHEET DATA
    Excel.application.Selection.Copy
   
  'ADD SHEET TO MAKE NEW TABLE FROM THE ABOVE COPY
    Excel.Sheets.Add
   
    'NAME SHEET - GRAB THE FIRST "NEW"
    Excel.Sheets(i + 1).Name = STRPLANTFILTER
   
    'SET NEW SHEET
    Set excelSheet2 = Excel.ActiveWorkbook.Sheets(STRPLANTFILTER)
   
    'SELECT NEW SHEET
    Excel.application.Sheets(STRPLANTFILTER).Select
   
    'SELECT THE FIRST CELL FOR PASTE
    Excel.ActiveWorkbook.Sheets(STRPLANTFILTER).Range("A1").Select
   
    'PASTE INTO CURRENT SHEET
    Excel.ActiveSheet.Paste
   
    'THAT MAKES A NEW TABLE FROM YOUR TARGET AUTOFILTERED DATA
End Sub

---

What you do with the returned data is then easier to do once it is isolated out per autofilter criteria needed.

@Skyscraper

Down to earth: never use 'select' or 'activate' in VBA.

Hi xSkysraper, thank you for your response. It's already midnight in Germany. Being out for a while. Will try tomorrow. Again, thank you for taking the time. Much appreciated.