Help with automating process?

im2bz2p345 · Feb 11, 2011

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

daverunt · Feb 11, 2011

Hi sorry about the worksheet name error - a common occurence.

I'm not too clear on what you are doing and what is happening with the 2 other cells you are looking up?

Here's a way of finding the target in the active worksheet - as long as it only appears once.

Code:

Target = "56833" 
 
    Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    For Each Cell In Rng
 
     If Cell.Value = Target Then
 
    End If
    Num = Cell.Row
 
Next Cell
    Range("AL147").Copy Destination:=ThisWorkbook.Sheets("PullFromTools").Range("C" & Num)

im2bz2p345 · Feb 14, 2011

Okay, I'm still having some trouble with this code. Here is a small sample of my "Master File."

Pull From Tools

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 42px"><COL style="WIDTH: 266px"><COL style="WIDTH: 107px"><COL style="WIDTH: 105px"><COL style="WIDTH: 90px"><COL style="WIDTH: 79px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 48px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 9pt; TEXT-ALIGN: center">Gross Patient A/R</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 9pt; TEXT-ALIGN: center">Contractual Reserve</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 9pt; TEXT-ALIGN: center">Bad Debt Reserve</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 9pt; TEXT-ALIGN: center">Denials Reserve</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 9pt; TEXT-ALIGN: center">Jan-11</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 9pt; TEXT-ALIGN: center">Jan-11</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 9pt; TEXT-ALIGN: center">Jan-11</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 9pt; TEXT-ALIGN: center">Jan-11</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: left">53833</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt">Blah</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">500.00 </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">500.00 </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">500.00 </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">500.00 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: left">53835</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt">BlahBlah</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">500.00 </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">500.00 </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">500.00 </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">500.00 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: left">53836</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt">BlahBlahBlah</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">500.00 </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">500.00 </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">500.00 </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">500.00 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: left">53837</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt">BlahBlahBlahBlah</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">500.00 </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">500.00 </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">500.00 </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">500.00 </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: left">53850</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt">BlahBlahBlahBlahBlah</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">500.00 </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">500.00 </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">500.00 </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">500.00 </TD></TR></TBODY></TABLE>

My code is below:

Code:

Sub Extract53833()
 
'Looks up the row associated with the business unit
    Target = "53833"
 
    Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    For Each Cell In Rng
 
    If Cell.Value = Target Then
 
    End If
    Num = Cell.Row
 
    Next Cell
 
'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
 
    Sheets("MonthlyDetail").Activate
 
    Range("AI142").Copy
    ThisWorkbook.Sheets("Pull From Tools").Range("C" & Num).PasteSpecial Paste:=xlPasteValues
    Range("AL147").Copy
    ThisWorkbook.Sheets("Pull From Tools").Range("D" & Num).PasteSpecial Paste:=xlPasteValues
    Range("AU147").Copy
    ThisWorkbook.Sheets("Pull From Tools").Range("E" & Num).PasteSpecial Paste:=xlPasteValues
    Range("BD147").Copy
    ThisWorkbook.Sheets("Pull From Tools").Range("F" & Num).PasteSpecial Paste:=xlPasteValues
 
'Close open source file
    Windows.Application.CutCopyMode = False
    SourceFile.Close False
 
End Sub

For some reason, it doesn't find "53833" in column A (row 3) and updates my last business unit "53840" (which is in Row 124).

Please help!

~ Im2bz2p345

P.S. - I also had one more question. If for example in Column A, my business unit was XXX53833XXX, how would I go about searching for "53833" amongst that? I don't know how to use wildcards in VBA. Would it be something like Target = "*"&"53833"&"*" ?

MrKowz · Feb 14, 2011

I believe it has to do with how you are setting rng (also, you did not dim any of your variables. I highly reccomend that you declare every variable you use).

Give this adjusted code a shot, it does not use any looping and gives you an input box to enter the unit you want to look up.

Code:

Sub Extract53833()
 
'Looks up the row associated with the business unit
    Dim num     As Variant, _
        LR      As Long, _
        target  As Long
 
'Disply InputBox for user to enter business unit
    target = InputBox("What business unit would you like to find?")
 
    LR = Range("A" & Rows.Count).End(xlUp).row
 
'Find the row the business unit appears on.
    num = Application.Match(target, Range("A1:A" & LR), 0)
 
'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
 
        Sheets("MonthlyDetail").Activate
 
        Range("AI142").Copy
        ThisWorkbook.Sheets("Pull From Tools").Range("C" & num).PasteSpecial Paste:=xlPasteValues
        Range("AL147").Copy
        ThisWorkbook.Sheets("Pull From Tools").Range("D" & num).PasteSpecial Paste:=xlPasteValues
        Range("AU147").Copy
        ThisWorkbook.Sheets("Pull From Tools").Range("E" & num).PasteSpecial Paste:=xlPasteValues
        Range("BD147").Copy
        ThisWorkbook.Sheets("Pull From Tools").Range("F" & num).PasteSpecial Paste:=xlPasteValues
 
    'Close open source file
        Windows.Application.CutCopyMode = False
        SourceFile.Close False
    Else
 
        MsgBox "The Business Unit was not found.  Please try again."
 
    End If
 
End Sub

im2bz2p345 · Feb 14, 2011

MrKowz,

Thank you SO much for your assistance. Your code works beautifully, BUT is there anyway to avoid having to ask the user "What business unit would you like to find?"

I have this same code setup for each Business Unit. All I want to have is for someone has to do is click a button next to the business unit that they want to update. The button will automatically run the macro, which will prompt the user to select the file they wish to use (to update the master file with). Everything else will be automated in that it will pull the values needed.

Just want to avoid having to ask the user "which business unit" when they are clicking a button next to the business unit they wish to update already.

Also, do you happen to know the answer to my other question:
If for example in Column A, my business unit was XXX53833XXX, how would I go about searching for "53833" amongst that? I don't know how to use wildcards in VBA. Would it be something like Target = "*"&"53833"&"*" ?
Hope that makes sense,

~ Im2bz2p345

MrKowz · Feb 14, 2011

Try this out:

Code:

Sub Extract53833()
 
'Looks up the row associated with the business unit
    Dim num     As Variant, _
        LR      As Long, _
        target  As Long
        
[COLOR=red][B]'Hardcode target value.  ~~~Do not wrap this value in quotes~~~
    target = 53833
[/B][/COLOR]    LR = Range("A" & Rows.Count).End(xlUp).row
'Find the row the business unit appears on.
    num = Application.Match[COLOR=red][COLOR=black]([/COLOR][B]"*" & target & "*"[/B][/COLOR][COLOR=black],[/COLOR] Range("A1:A" & LR), 0)
'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
     
        Sheets("MonthlyDetail").Activate
     
        Range("AI142").Copy
        ThisWorkbook.Sheets("Pull From Tools").Range("C" & num).PasteSpecial Paste:=xlPasteValues
        Range("AL147").Copy
        ThisWorkbook.Sheets("Pull From Tools").Range("D" & num).PasteSpecial Paste:=xlPasteValues
        Range("AU147").Copy
        ThisWorkbook.Sheets("Pull From Tools").Range("E" & num).PasteSpecial Paste:=xlPasteValues
        Range("BD147").Copy
        ThisWorkbook.Sheets("Pull From Tools").Range("F" & num).PasteSpecial Paste:=xlPasteValues
     
    'Close open source file
        Windows.Application.CutCopyMode = False
        SourceFile.Close False
    End If
End Sub

im2bz2p345 · Feb 14, 2011

Alright, I tried your code, but I'm getting the following error when I step through: http://img808.imageshack.us/img808/1858/errord.png

This is SO frustrating!

Hope you can help figure out what I'm doing wrong,

~ Im2bz2p345

MrKowz · Feb 14, 2011

I did some testing with wildcards and using the worksheet function outside of VBA, and it appears that we do actually need to wrap the value in quotes:

Code:

Sub Extract53833()
 
'Looks up the row associated with the business unit
    Dim num     As Variant, _
        LR      As Long, _
        [B][COLOR=red]target  As String[/COLOR][/B]
        
[COLOR=red][B]'Hardcode target value.  ~~~Wrap this value in quotes~~~
[/B][/COLOR]    [B][COLOR=red]target = "53833"[/COLOR][/B]
    LR = Range("A" & Rows.Count).End(xlUp).row
'Find the row the business unit appears on.
    num = Application.Match("*" & target & "*", Range("A1:A" & LR), 0)
'If the unit is found, continue with code.  Otherwise, Exit Sub.

    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
     
        Sheets("MonthlyDetail").Activate
     
        Range("AI142").Copy
        ThisWorkbook.Sheets("Pull From Tools").Range("C" & num).PasteSpecial Paste:=xlPasteValues
        Range("AL147").Copy
        ThisWorkbook.Sheets("Pull From Tools").Range("D" & num).PasteSpecial Paste:=xlPasteValues
        Range("AU147").Copy
        ThisWorkbook.Sheets("Pull From Tools").Range("E" & num).PasteSpecial Paste:=xlPasteValues
        Range("BD147").Copy
        ThisWorkbook.Sheets("Pull From Tools").Range("F" & num).PasteSpecial Paste:=xlPasteValues
     
    'Close open source file
        Windows.Application.CutCopyMode = False
        SourceFile.Close False
    End If
End Sub

im2bz2p345 · Feb 14, 2011

Thanks for your continued help MrKowz, but for some reason, I get the exact same error as in the screenshot in my previous post (Error 2042). When doing some research on the error, it seems like it's similar to the #NA error you get when a match isn't found.

Here is some more info: http://www.mrexcel.com/forum/showthread.php?t=428825

I can't figure out why a match isn't being found though. I doesn't have to do with the format my numbers are in (tried playing around with that already).

Any ideas?

~ Im2bz2p345

MrKowz · Feb 14, 2011

Are you sure a match is supposed to be found? If so, what exactly is contained in the cell it is supposed to be found in?

im2bz2p345 · Feb 14, 2011

Okay I did some further testing. This seems to be working:

Rich (BB code):

Sub Extract53833()
 
'Looks up the row associated with the business unit
    Dim num     As Variant, _
        LR      As Long, _
        target  As Long
 
'Hardcode target value.  ~~~Wrap this value in quotes~~~
    target = "53833"
    LR = Range("A" & Rows.Count).End(xlUp).Row
'Find the row the business unit appears on.
    num = Application.Match(target, Range("A1:A" & LR), 0)
'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("MonthlyDetail").Activate
 
        Range("AI142").Copy
        ThisWorkbook.Sheets("Pull From Tools").Range("C" & num).PasteSpecial Paste:=xlPasteValues
        Range("AL147").Copy
        ThisWorkbook.Sheets("Pull From Tools").Range("D" & num).PasteSpecial Paste:=xlPasteValues
        Range("AU147").Copy
        ThisWorkbook.Sheets("Pull From Tools").Range("E" & num).PasteSpecial Paste:=xlPasteValues
        Range("BD147").Copy
        ThisWorkbook.Sheets("Pull From Tools").Range("F" & num).PasteSpecial Paste:=xlPasteValues
 
    'Close open source file
        Windows.Application.CutCopyMode = False
        SourceFile.Close False
    End If
 
End Sub

Whether I wrap the target (in this case "53833") in quotes or not has NO IMPACT. It runs fine now and num is set to 3 (like it should be).

Here is what it looks like when working: http://img140.imageshack.us/img140/3066/workedv.png

Apparently I can't do wildcards though. If I try adding the "*" & target & "*", I get the Error 2042 (#NA).

Any ideas how wildcards can be used in VBA? Maybe it's not possible with this code.

~ Im2bz2p345

Help with automating process?

im2bz2p345

Board Regular

daverunt

Well-known Member

Excel Facts

im2bz2p345

Board Regular

MrKowz

Well-known Member

im2bz2p345

Board Regular

MrKowz

Well-known Member

im2bz2p345

Board Regular

MrKowz

Well-known Member

im2bz2p345

Board Regular

MrKowz

Well-known Member

im2bz2p345

Board Regular

Similar threads

Forum statistics

Share this page

Help with automating process?

Board Regular

Well-known Member

Excel Facts

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock