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

 

[im2bz2p345]

Hi, this is wrong

target  As String [COLOR=red][B]_[/B][/COLOR]
[COLOR=black]or[/COLOR]
[COLOR=red][B]Dim[/B] [/COLOR][COLOR=black]lastmonth As String[/COLOR]

Also there is a problem with; I think an & is missing here but it still has a problem that I haven't worked out.

Cells(Application.Match("Balance at" [COLOR=red][B]&[/B][/COLOR] lastmonth, Range("B:B"), 0), 2).Copy

I think MrKowz cracked it already.

Sorry just saw your post dave,

Thanks for the correction. I added the & as you mentioned, but unfortunately, I still get that error: Run-time error '13' (Type mismatch)

My code is:

Cells(Application.Match("Balance at " & lastmonth, Range("B:B"), 0), 2).Copy

I posted a sample of my source file if that helps at all.

Not sure why it's screwing up,

~ Im2bz2p345

 

[daverunt]

Hi,

Mr Kowz already posted the fix for that and it works for me:
Make sure what you are copying has as many spaces as the code is looking at i.e 1 space between at and the date.
In code it's the space between at and "



The code expects Balance at <SPACE>31/02/11

Cells(Application.Match("Balance [COLOR=red][B]at <SPACE>" &[/B][/COLOR] lastmonth, Range("B:B"), 0), 2).Copy

Also this isn't tied to any other code so you can put it at the top of the macro as the first thing for the user to do.

lastmonth = InputBox("Please enter the last day of the previous month in this format MM/DD/YY")

 

[im2bz2p345]

I still get that same error I have even tried to remove the "lastmonth" stuff and simply put "Balance at 01/31/11" without a success.

Here is my complete code:

Sub Extract16122DENVERPRO()
'Looks up the row associated with the business unit
    Dim num     As Variant, _
        LR      As Long, _
        target  As String, _
        lastmonth As String
        
'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
        Sheets("AR ROLLFORWARD").Activate
           Cells(Application.Match("Balance at " & lastmonth, Range("B:B"), 0), 2).Copy
           ThisWorkbook.Sheets("Pull From Tools").Range("C" & num).PasteSpecial Paste:=xlPasteValues
        
        Sheets("RESERVE ROLLFORWARD").Activate
           Cells(Application.Match("Balance at " & lastmonth, Range("B:B"), 0), 2).Copy
           ThisWorkbook.Sheets("Pull From Tools").Range("D" & num).PasteSpecial Paste:=xlPasteValues
             
    'Close open source file
        Windows.Application.CutCopyMode = False
        SourceFile.Close False
    End If
End Sub

When I step through the code, it gives me the error at the following line in red above.

Any ideas?

~ Im2bz2p345

 

[daverunt]

Try replacing

[COLOR=red]"Balance at " & lastmonth,[/COLOR]
 
 
With
 
 
[COLOR=red]Range("Bx").Value,[/COLOR] (where x is the cell number of the latest date to be copied).

And see what is copied across.

Is the 'Balance at 01/31/11' actually in one cell - the code expects it to be?

 

[im2bz2p345]

Hi dave,

It works perfect now, unfortunately I can't keep the cell value "B13" in the code because every month this will change. Can you figure out why it wasn't working with the "Balance at 01/31/2011" (when prompted to be entered by the user)? I had posted a sample from my source file earlier and indeed the "Balance at xxxxx" statements are in column A in one cell (not broken apart).

Very frustrating!


My code

Sub Extract16122DENVERPRO()
'Looks up the row associated with the business unit
    Dim num     As Variant, _
        LR      As Long, _
        target  As String, _
        lastmonth As String
 
'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
        Sheets("AR ROLLFORWARD").Activate
           Cells(Application.Match(Range("B13").Value, Range("B:B"), 0), 2).Copy
           ThisWorkbook.Sheets("Pull From Tools").Range("C" & num).PasteSpecial Paste:=xlPasteValues
 
        Sheets("RESERVE ROLLFORWARD").Activate
           Cells(Application.Match(Range("B13").Value, Range("B:B"), 0), 2).Copy
           ThisWorkbook.Sheets("Pull From Tools").Range("D" & num).PasteSpecial Paste:=xlPasteValues
 
    'Close open source file
        Windows.Application.CutCopyMode = False
        SourceFile.Close False
    End If
End Sub

 

[daverunt]

Doh!

Problem revealed. I'm actually copying the date match in Column A, which is why it appears to be working for me.

I.m not sure exactly what the end part of this line is doing (in red) but you need to copy the cell offset once the match is found and this line either doesn't or isn't doing it.
Mr Kowz if you're reading this can you explain?

Cells(Application.Match("Balance at " & lastmonth, Range("B:B"),[COLOR=red][B] 0), 2).[/B][/COLOR]Copy

I have managed to get it to work but the code isn't tidy. Also I have had to offset by 2 columns on my worksheet for some reason I can't fathom - you may have to change that to (0, 1)

Sheets("AR ROLLFORWARD").Activate
          Cells(Application.Match("Balance at " & lastmonth, Range("B:B"), 0), 2).Activate
          ActiveCell.[COLOR=red]Offset(0, 2).[/COLOR]Copy
          ThisWorkbook.Sheets("Pull From Tools").Range("C" & num).PasteSpecial Paste:=xlPasteValues

 

[daverunt]

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.

Sheets("AR ROLLFORWARD").Activate
          Cells(Application.Match("Balance at " & lastmonth, Range("B:B"), 0), 2).Activate
          ActiveCell.Offset(0, 2).Copy Destination:=ThisWorkbook.Sheets("Pull From Tools").Range("C" & num)

 

[im2bz2p345]

I have managed to get it to work but the code isn't tidy. Also I have had to offset by 2 columns on my worksheet for some reason I can't fathom - you may have to change that to (0, 1)

Sheets("AR ROLLFORWARD").Activate
          Cells(Application.Match("Balance at " & lastmonth, Range("B:B"), 0), 2).Activate
          ActiveCell.[COLOR=red]Offset(0, 2).[/COLOR]Copy
          ThisWorkbook.Sheets("Pull From Tools").Range("C" & num).PasteSpecial Paste:=xlPasteValues

I will try this out today and get back to you dave! Thanks for your continous help on this issue.

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.

 Sheets("AR ROLLFORWARD").Activate
          Cells(Application.Match("Balance at " & lastmonth, Range("B:B"), 0), 2).Activate
          ActiveCell.Offset(0, 2).Copy Destination:=ThisWorkbook.Sheets("Pull From Tools").Range("C" & num)

The Application.Match code was given to me by MrKowz. I will PM him and hope that he has a chance to come across this thread again. He'll probably be the best person to explain the parameters of the function, since my Google searches haven't been much help either.

~ Im2bz2p345

 

[im2bz2p345]

Hi,

A bit tidier.

Sheets("AR ROLLFORWARD").Activate
          Cells(Application.Match("Balance at " & lastmonth, Range("B:B"), 0), 2).Activate
          ActiveCell.Offset(0, 2).Copy Destination:=ThisWorkbook.Sheets("Pull From Tools").Range("C" & num)

I am still unable to get this code to work. I get the same run-time error '13' on the same line as before: http://img683.imageshack.us/img683/5696/matchnotfound.png

Not sure what the problem is, but if anyone can help, that would be a huge help.

~ Im2bz2p345

 

[daverunt]

Hi, sorry the match you are trying to make is in Column A.

I have a bodged up set of files I am testing on and should have changed it to suit your code.

Walk it through the code using F8 so you can follow where it's copying from. You may have to change the offset number as stated previously.

Cells(Application.Match("Balance at " & lastmonth, Range("A:A), 0), 2).Activate