Consulting

Results 1 to 6 of 6

Thread: VBA Code Copy&Paste Request

  1. #1
    VBAX Regular
    Joined
    Apr 2011
    Posts
    7
    Location

    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
    Attached Files Attached Files

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,148
    Location
    What have you tried? After all, you've made well over 100 posts to date in various forums, treating them like free coding services.
    Last edited by macropod; 10-26-2020 at 02:06 PM.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,967
    @Macropod

    Thanks for your attentiveness and warning us

  4. #4
    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.

  5. #5
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,967
    @Skyscraper

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

  6. #6
    VBAX Regular
    Joined
    Apr 2011
    Posts
    7
    Location
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •