+ Reply to Thread
Results 1 to 4 of 4

VBA Code A task complete Task as Code B

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-30-2013
    Location
    washington
    MS-Off Ver
    Excel 2019
    Posts
    168

    VBA Code A task complete Task as Code B

    Dear experts

    I have set of macro to export rows to closed wb's depends upon file name meet wb's in column I:I in CodeA

    and I have set of macro to export particular lines export to closed wb's depends upon file name meet wb's in column H:H in CodeB

    but task is i want run CodeB task as CodeA procedure.

    e.g take the wb's names from col I:I compare file in path move rows to correponding file as B

    Here CodeA
    
    Sub codea()
    Dim mypath As String, wb As String, lastrow As Long
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Range("I:I").Copy Range("Z:Z")
    Range("Z:Z").RemoveDuplicates Columns:=1, Header:=xlYes
    lastrow = Range("Z" & Rows.Count).End(xlUp).Row
    For x = 2 To lastrow
        Range("A:I").AutoFilter field:=9, Criteria1:=Range("Z" & x)
        With Range("A1").CurrentRegion
            .Offset(1).Copy
            wb = Range("Z" & x)
            Workbooks.Open (mypath & "\" & wb)
            Sheets("Master").Activate
            With Range("F" & Rows.Count).End(xlUp).Offset(1)
                .PasteSpecial Paste:=xlValues
            End With
         End With
        ActiveWorkbook.Save
        ActiveWorkbook.Close
    Next
    Range("A:I").AutoFilter
    With Range("A1").CurrentRegion
        .Offset(1).Delete
    End With
    HERE CodeB

    Sub CodeB()
    
    Dim wbA As Workbook, wbB As Workbook, wbM As Workbook, wb As Workbook
    Dim sFilePath As String, rCell As Range, sWB As String
    
    Set wbM = ThisWorkbook 'The Master Workbook
    sFilePath = "C:\Test" 'hard coded version (left unchanged from your code sample)
    
    For Each wb In Excel.Workbooks
         If wb.Name = "A.xlsb" Then Set wbA = wb
         If wb.Name = "B.xlsb" Then Set wbB = wb
         Next
    If wbA Is Nothing Then Set wbA = Excel.Workbooks.Open(Filename:=sFilePath & "\" & "A.xlsb") 'Amend as necessary
    If wbB Is Nothing Then Set wbB = Excel.Workbooks.Open(Filename:=sFilePath & "\" & "B.xlsb") 'Amend as necessary
    
    For Each rCell In wbM.Worksheets("Master").Range("A2:A200")
         If InStr(rCell, "-451414-") > 0 Then
              sWB = rCell.Offset(0, 7)
              If sWB = "A.xlsb" Or sWB = "B.xlsb" Then
                   With Excel.Workbooks(sWB)
                        .Worksheets("REK").Range("E65000").End(xlUp).Offset(1).Resize(, 12) = _
                        Application.Index(Split(Join(Array(Replace$(Replace$(rCell, "-0-", "-000000-"), "-", "|"), "00000000", "000", "MAY-15", "USD", "AMOUNT", rCell.Offset(, 3), "NO"), "|"), "|"), Array(1, 3, 2, 4, 5, 6, 7, 8, 9, 10, 11, 12))
                        End With
                   End If
              End If
         Next
    wbA.Close SaveChanges:=True
    wbB.Close SaveChanges:=True
    End Sub
    find the attachment and wb TASKA&B have sheet tabs A,B,C,D,E,F consider as workbook saved in path
    path :C:\Users\Admin\Desktop\test.
    Attached Files Attached Files

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,864

    Re: VBA Code A task complete Task as Code B

    As for direct answer for this part: "but task is i want run CodeB task as CodeA procedure.", I'd say: in a row of codea where you need it, insert:
    Formula: copy to clipboard
    Call CodeB
    Best Regards,

    Kaper

  3. #3
    Forum Contributor
    Join Date
    04-30-2013
    Location
    washington
    MS-Off Ver
    Excel 2019
    Posts
    168

    Re: VBA Code A task complete Task as Code B

    Dear sir

    i want rows export to closed wb A,B,D,F....depends upon name of workbook listed in column I:I filter value as per name then task end with criteria is split across 0062-451414-0-XM000-0000 those value as result in C11

    this taks is required what code B does
    For Each rCell In wbM.Worksheets("Master").Range("A2:A200")
         If InStr(rCell, "-451414-") > 0 Then
              sWB = rCell.Offset(0, 7)
              If sWB = "A.xlsb" Or sWB = "B.xlsb" Then
                   With Excel.Workbooks(sWB)
                        .Worksheets("REK").Range("E65000").End(xlUp).Offset(1).Resize(, 12) = _
                        Application.Index(Split(Join(Array(Replace$(Replace$(rCell, "-0-", "-000000-"), "-", "|"), "00000000", "000", "MAY-15", "USD", "AMOUNT", rCell.Offset(, 3), "NO"), "|"), "|"), Array(1, 3, 2, 4, 5, 6, 7, 8, 9, 10, 11, 12))
                        End With

  4. #4
    Forum Contributor
    Join Date
    04-30-2013
    Location
    washington
    MS-Off Ver
    Excel 2019
    Posts
    168

    Re: VBA Code A task complete Task as Code B

    experts any help?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA code to automate simple task
    By itselflearn in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-20-2013, 12:11 PM
  2. VB Code to open pdf file do some task
    By saila in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-23-2012, 06:48 AM
  3. need code for the particular task
    By accountant. in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-31-2011, 05:53 AM
  4. Code to work out working days a task took
    By ianma in forum Excel General
    Replies: 1
    Last Post: 10-21-2009, 10:44 AM
  5. Code for monthly task
    By mingali in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-23-2009, 10:14 AM
  6. Schedule task code
    By Sam.K in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-06-2008, 02:00 AM
  7. [SOLVED] Need a simple VBA code to automate a repetitive task.
    By madhu in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-21-2005, 07:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1