Help with automating process?

[im2bz2p345]

Hey all,

I've used & referenced this board a lot in the past for some great help! I have a situation now that I need few suggestions on if it's possible for me to "automate" any steps.

What I have is a sheet called "Pull from Tools" in "Workbook A" which I need to update with the latest numbers from a business unit file. Every month I have to look for the most recent version of "Workbook B - XXXXX<INSERT here number Unit Business>" (the X's represent a particular business unit number). In that specific business unit's workbook B, I need to go to the "MonthlyDetail" sheet and pull the numbers from the following cell references: AL147, AU147, BD147.

I would just link them and do a "Find" and "Replace" to update the file's location (since each month has it's own folder), but the problem is that sometimes there might two files with the same Business Unit and I would need to get the information from the more updated one (newer one).

Currently I'm having to manually open up each business unit's monthly file, go to the "MonthlyDetail" sheet, find the values in AL147, AU147, BD147.. then copy-paste them into my main workbook that I'm working from (Workbook A). Is there any way to automate this at all? Would using some type of macro help? Is it possible to set it up so that I can just browse & select a workbook every month and it will automatically spit out those three values for me?

Any help would be greatly appreciated,

~ Im2bz2p345

 

[MrKowz]

Hi,


A bit tidier.
I have been trying to find info on Application.Match but no luck.
Where did you get the info? I would like to know what the parameters mean.

Application.Match is replicating the function MATCH that you can use at the worksheet level. I used Application.Match instead of WorksheetFunction.Match so that any error it returns can be trapped by a variable, and tested.

What you'll likely need to do with this is create another variable, similar to num, and store what the appliaction.match returns as that variable, and test to see if it errors:

Sub Extract16122DENVERPRO()
'Looks up the row associated with the business unit
    Dim num             As Variant, _
        LR              As Long, _
        target          As String, _
        lastmonth       As String, _
        [B][COLOR=red]lastmonthrow    As Variant[/COLOR][/B]
 
'Prompt user for last day for previous month
    lastmonth = InputBox("Please enter the last day of the previous month in this format MM/DD/YY")
'Hardcode target value.
    target = "DENVER PRO"
    LR = Range("B" & Rows.Count).End(xlUp).row
'Find the row the business unit appears on.
    num = Application.Match(target, Range("B1:B" & LR), 0)
'If no match is found with wildcards, the next match will search for when the business unit is the only thing in the cell.
    If IsError(num) Then
        num = Application.Match(target, Range("B1:B" & LR), 0)
    End If
'If the unit is found, continue with code.  If the unit is not found, display a message box.
    If Not IsError(num) Then
 
 
    'Display Open Dialog to select  file
        SourceFile = Application.GetOpenFilename("Excel Files (*.xls*)," & _
        "*.xlsx*", 1, "Select File", "Open", False)
 
 
    'If the user cancels file selection then exit
        If TypeName(SourceFile) = "Boolean" Then
            Exit Sub
        End If
 
    'Sets source workbook as active, copies from source, then paste-specials the values back into master file
        Workbooks.Open SourceFile
        Set SourceFile = ActiveWorkbook
        
           [B][COLOR=red]Sheets("AR ROLLFORWARD").Activate
           lastmonthrow = Application.Match("Balance at " & lastmonth, Range("B:B"), 0)
           If Not IsError(lastmonthrow) Then
              Cells(lastmonthrow, 2).Copy
              ThisWorkbook.Sheets("Pull From Tools").Range("C" & num).PasteSpecial Paste:=xlPasteValues
           Else
              MsgBox "No match was found for " & lastmonth & " in the worksheet ""AR ROLLFORWARD"""
           End If
           Sheets("RESERVE ROLLFORWARD").Activate
           lastmonthrow = Application.Match("Balance at " & lastmonth, Range("B:B"), 0)
           If Not IsError(lastmonthrow) Then
              Cells(lastmonthrow, 2).Copy
              ThisWorkbook.Sheets("Pull From Tools").Range("C" & num).PasteSpecial Paste:=xlPasteValues
           Else
              MsgBox "No match was found for " & lastmonth & " in the worksheet ""RESERVE ROLLFOWARD"""
           End If[/COLOR]
[/B] 
    'Close open source file
        Windows.Application.CutCopyMode = False
        SourceFile.Close False
    End If
End Sub

 

[daverunt]

Hi

Thanks for the MATCH info.

 

[im2bz2p345]

Thanks for the explanation and help MrKowz.

Here is my code with the changes that I made (in red) to your code above:

Sub Extract16122DENVERPRO()
'Looks up the row associated with the business unit
    Dim num     As Variant, _
        LR      As Long, _
        target  As String, _
        lastmonth As String, _
        lastmonthrow As Variant
 
'Prompt user for last day for previous month
    lastmonth = InputBox("Please enter the last day of the previous month in this format MM/DD/YY")
'Hardcode target value.
    target = "DENVER PRO"
    LR = Range("B" & Rows.Count).End(xlUp).Row
'Find the row the business unit appears on.
    num = Application.Match(target, Range("B1:B" & LR), 0)
'If no match is found with wildcards, the next match will search for when the business unit is the only thing in the cell.
    If IsError(num) Then
        num = Application.Match(target, Range("B1:B" & LR), 0)
    End If
'If the unit is found, continue with code.  If the unit is not found, display a message box.
    If Not IsError(num) Then
 
 
    'Display Open Dialog to select  file
        SourceFile = Application.GetOpenFilename("Excel Files (*.xls*)," & _
        "*.xls*", 1, "Select File", "Open", False)
 
 
    'If the user cancels file selection then exit
        If TypeName(SourceFile) = "Boolean" Then
            Exit Sub
        End If
 
    'Sets source workbook as active, copies from source, then paste-specials the values back into master file
        Workbooks.Open SourceFile
        Set SourceFile = ActiveWorkbook
        Sheets("AR ROLLFORWARD").Activate
           lastmonthrow = Application.Match("Balance at " & lastmonth, Range("A:A"), 0)
           If Not IsError(lastmonthrow) Then
              Cells(lastmonthrow, 2).Copy
              ThisWorkbook.Sheets("Pull From Tools").Range("C" & num).PasteSpecial Paste:=xlPasteValues
           Else
              MsgBox "No match was found for " & lastmonth & " in the worksheet ""AR ROLLFORWARD"""
           End If
 
        Sheets("RESERVE ROLLFORWARD").Activate
           lastmonthrow = Application.Match("Balance at " & lastmonth, Range("A:A"), 0)
           If Not IsError(lastmonthrow) Then
              Cells(lastmonthrow, 2).Copy
              ThisWorkbook.Sheets("Pull From Tools").Range("D" & num).PasteSpecial Paste:=xlPasteValues
           Else
              MsgBox "No match was found for " & lastmonth & " in the worksheet ""RESERVE ROLLFOWARD"""
           End If
 
    'Close open source file
        Windows.Application.CutCopyMode = False
        SourceFile.Close False
    End If
End Sub

I get the following errors:

http://ploader.net/files/86134086c9fe2eb5e1ac721fd30b351d.png

http://ploader.net/files/3954938ea7700f074d975a0b1fae5454.png



I had posted a sample of my source file, back on page 3 of this thread (http://www.mrexcel.com/forum/showthread.php?p=2615168#post2615168) if it has something to do with that.

~ Im2bz2p345

 

[MrKowz]

Lastdate isn't coming over in mm/dd/yyyy format. Try this, it coerces the number into that format:

Sub Extract16122DENVERPRO()
'Looks up the row associated with the business unit
    Dim num     As Variant, _
        LR      As Long, _
        target  As String, _
        lastmonth As String, _
        lastmonthrow As Variant
 
'Prompt user for last day for previous month
    [COLOR=red][B]lastmonth = Application.Text(InputBox("Please enter the last day of the previous month in this format MM/DD/YY"), "mm/dd/yyyy")
[/B][/COLOR]'Hardcode target value.
    target = "DENVER PRO"
    LR = Range("B" & Rows.Count).End(xlUp).row
'Find the row the business unit appears on.
    num = Application.Match(target, Range("B1:B" & LR), 0)
'If no match is found with wildcards, the next match will search for when the business unit is the only thing in the cell.
    If IsError(num) Then
        num = Application.Match(target, Range("B1:B" & LR), 0)
    End If
'If the unit is found, continue with code.  If the unit is not found, display a message box.
    If Not IsError(num) Then
 
 
    'Display Open Dialog to select  file
        SourceFile = Application.GetOpenFilename("Excel Files (*.xls*)," & _
        "*.xls*", 1, "Select File", "Open", False)
 
 
    'If the user cancels file selection then exit
        If TypeName(SourceFile) = "Boolean" Then
            Exit Sub
        End If
 
    'Sets source workbook as active, copies from source, then paste-specials the values back into master file
        Workbooks.Open SourceFile
        Set SourceFile = ActiveWorkbook
        Sheets("AR ROLLFORWARD").Activate
           lastmonthrow = Application.Match("Balance at " & lastmonth, Range("A:A"), 0)
           If Not IsError(lastmonthrow) Then
              Cells(lastmonthrow, 2).Copy
              ThisWorkbook.Sheets("Pull From Tools").Range("C" & num).PasteSpecial Paste:=xlPasteValues
           Else
              MsgBox "No match was found for " & lastmonth & " in the worksheet ""AR ROLLFORWARD"""
           End If
 
        Sheets("RESERVE ROLLFORWARD").Activate
           lastmonthrow = Application.Match("Balance at " & lastmonth, Range("A:A"), 0)
           If Not IsError(lastmonthrow) Then
              Cells(lastmonthrow, 2).Copy
              ThisWorkbook.Sheets("Pull From Tools").Range("D" & num).PasteSpecial Paste:=xlPasteValues
           Else
              MsgBox "No match was found for " & lastmonth & " in the worksheet ""RESERVE ROLLFOWARD"""
           End If
 
    'Close open source file
        Windows.Application.CutCopyMode = False
        SourceFile.Close False
    End If
End Sub

 

[im2bz2p345]

Thank you so much Mr. Kowz! With the help of yourself and dave, I manage to get it working.

Dave has suggested that I change the code to "mm/dd/yy" and apparently in the screenshots of the error that I took above, I had typed the month as "1" instead of "01"

I greatly apprecite the both you helpin me throughout this time. This marco should hopefully do me wonders! Thank you again!!

~ Im2bz2p345