Help with automating process?

I

im2bz2p345

Board Regular
Joined
Mar 31, 2008
Messages
229
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 :)
 
Last edited:

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
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.

Code: 
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
 
Upvote 0
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:

Code: 
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 :)
 
Upvote 0
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 :)
 
Last edited:
Upvote 0
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)
 
Upvote 0
daverunt said:
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)
Click to expand...

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

Unfortunately the code gives me this error below:

runtimeerrorj.jpg


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 :)
 
Upvote 0
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 :)
 
Upvote 0
Try like this

Code: 
Range("AL147").Copy
ThisWorkbook.Sheets("PullFromTools").Range("A1").PasteSpecial Paste:=xlPasteValues
 
Upvote 0
VoG said:
Try like this

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

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 :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Automated Reporting - From Excel to PPT
Replies
1
Views
60
Rijnsent
Rijnsent
C
  • Question Question
Organisational Grade and Position Mapping via VBA or other excel wizardry
Replies
1
Views
181
arun_eagertolearn
A
B
  • Question Question
Excel Table Lookup with OR Conditions and Multiple Columns
Replies
1
Views
127
BenW71
B
W
  • Question Question
Macro - Auto-email for monthly KPIs updates across all departments
Replies
2
Views
395
cwstudio2020
C
N
  • Question Question
How can I sort rows into different worksheets?
Replies
2
Views
141
nithchun
N

Forum statistics

Threads
1,224,602
Messages
6,179,841
Members
452,948
Latest member
UsmanAli786

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top