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

 

[daverunt]

Hi,

here's - hopefully - a start.

This goes in Workbook A.
You select the file from the FileOpen dialog dialog and the values are extracted to the sheet required on WkBk A cells A1 - A3 - (you need to change that).

Try on file copy.

Sub Extract()
'Display Open Dialog to select  file
    SourceFile = Application.GetOpenFilename("Excel Files (*.xlsx*)," & _
    "*.xlsx*", 1, "Select File", "Open", False)
 
'If the user cancels file selection then exit
    If TypeName(SourceFile) = "Boolean" Then
        Exit Sub
    End If
 
    Workbooks.Open SourceFile
    Set SourceFile = ActiveWorkbook
 
    Sheets("MonthlyDetail").Activate
 
    Range("AL147").Copy Destination:=ThisWorkbook.Sheets("PullFromTools").Range("A1")
    Range("AU147").Copy Destination:=ThisWorkbook.Sheets("PullFromTools").Range("A2")
    Range("BD147").Copy Destination:=ThisWorkbook.Sheets("PullFromTools").Range("A3")
 
'Close open source file
    Windows.Application.CutCopyMode = False
    SourceFile.Close False
 
End Sub

 

[im2bz2p345]

I did some research and found this article (http://spreadsheetpage.com/index.php/tip/a_vba_function_to_get_a_value_from_a_closed_file/) which seems to take me into the right direction.

I am just very new to VB coding, so I don't know where to put the following values from the example:

p = "c:\XLFiles\Budget"
f = "Budget.xls"
s = "Sheet1"
a = "A1"

I would need to update/change the p & f values on a monthly basis, so is there any way that I can put them in Excel and copy-paste the new path every month/filename every month? Also, is it possibly to add this to a button so that after I've done all of this, I can just press an "Update" button which will run the code and update the values that I need?

~ Im2bz2p345

 

[im2bz2p345]

Sorry, just saw your response dave as I was posting.

Is there a way that I can assign your code to a button? Because in the "PullfromTools" sheet on my main workbook (Workbook A), there are about 50 or more business units that I need to do this for.

Appreciate your guidance,

~ Im2bz2p345

 

[daverunt]

You could add a button (form control) to a sheet and assign the macro to it.

Look in Excel Options> Customise and you can add the control to the Quick Access oolbar.

You can then just click on it and drag your required button size onto the sheet and assign the macro to it (right-click)

 

[im2bz2p345]

You could add a button (form control) to a sheet and assign the macro to it.

Look in Excel Options> Customise and you can add the control to the Quick Access oolbar.

You can then just click on it and drag your required button size onto the sheet and assign the macro to it (right-click)

Thanks! I figured it out while I waiting for a response actually.

Unfortunately the code gives me this error below:

When I click on the debug button, it points to this line:

Range("AI142").Copy Destination:=ThisWorkbook.Sheets("PullFromTools").Range("C3")

I had added this line in [above your Range ("AL142") line]. The macro opens up the workbook that I selected, but I get stuck at this line.

Could you please help? Thank you.

~ Im2bz2p345

 

[im2bz2p345]

Hello dave,

I tweaked your code slightly. You had ThisWorkbook.Sheets("PullFromTools") while it was supposed to be ThisWorkbook.Sheets("Pull From Tools"). The spaces are what caused the Run-time Error '9' above since it wasn't able to locate that worksheet before. I have fixed this now.

Your code works AMAZING! Thank you SO much for all of your effort. This will save me a ton of time.

The only problem that I have now is when the macro pulls it in the cell's data, it copies their format as well (the font is in blue and it's underlined twice). Is there any way to adjust your code so that it pulls in just the values instead of the format & value?

Appreciate your help greatly once again,

~ Im2bz2p345

 

[VoG]

Try like this

Range("AL147").Copy
ThisWorkbook.Sheets("PullFromTools").Range("A1").PasteSpecial Paste:=xlPasteValues

 

[im2bz2p345]

Try like this

Range("AL147").Copy
ThisWorkbook.Sheets("PullFromTools").Range("A1").PasteSpecial Paste:=xlPasteValues

Thank you SO much VoG! You're a life saver.

I only have one small request with this code. Is it possibly instead of using a "set" range such as .Range("C3"), there could be a lookup/match performed? For example, in column A I have different business units/sites such as listed below:

56883
HOPE
13062
ORAG(2215)

It would be great if in the VB code, I could specify a site and it could set the range to the row it found it on. The column numbers that need to be updated (in my original workbook) are always C, D, E, and F.

So for example, for the module that I create for 56833, it would do a lookup of "56833" in Column A. If found, it would take the row it's on [=ROW()] and add C before it. So if "56833" was in the third row, it would be "C3" <-- the lookup would come up with the C.

Any help on how to do this would be appreciated!

~ Im2bz2p345

 

[im2bz2p345]

Sorry, in my last sentence, I meant "the lookup would come up with the 3 (the row)" above.