VBA Code Copy&Paste Request

[rsrasc]

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

[macropod]

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

[snb]

@Macropod

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

[xSkyscraper]

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


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.

[snb]

@Skyscraper

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

[rsrasc]

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.