Use VBA to create a formula to a different Workbook

mjohnston0209

New Member
Joined
Nov 6, 2017
Messages
46
I am having difficulty creating a formula that will reference a value in one cell of a different workbook.

Say for example, I want to pull the value of Cell A1 (Tab name = Project Summary) using a relative reference from the first file below and enter it into Cell A1 (Tab name = Rates) of the second file. I want to round the pulled number to 2 decimal points and then copy the formula into several other cells.

1) W:\Accounting\Financial Reporting\WIPS\Hawk_TC2023153.xlsm
2) W:\Accounting\Financial Reporting\WIPS\Change Orders\Hawk - TC2023153.xlsx

Below is the coding I currently use which does work. The flaw with it is that by copying and pasting the information from one file to another, I am unable to round the values.

VBA Code:
Dim myCOExtension As String

Dim wbWIP As Workbook
Dim WIPName As String
Dim myWIPPath As String
Dim MyWIPFile As String


'Optimize Macro Speed
  Application.ScreenUpdating = False

'Identify File paths
  myCOPath = "W:\Accounting\Financial Reporting\WIPS\Change Orders\"
  myWIPPath = "W:\Accounting\Financial Reporting\WIPS\"

'Target File Extension (must include wildcard "*")
  myExtension = "*.xlsx*"

'Target Path with Ending Extention
  MyCOFile = Dir(myCOPath & myCOExtension)

'Loop through each Excel file in folder
  Do While MyCOFile <> ""
    'Set variable equal to opened workbook
      Set wb = Workbooks.Open(Filename:=myCOPath & MyCOFile)
      
    'Open WIP File
      WIPName = Sheets("Rates").Range("B2") & "_" & Sheets("Rates").Range("B1") & ".xlsm"
      MyWIPFile = myWIPPath & WIPName
      Set wbWIP = Workbooks.Open(MyWIPFile)
    
    'Select WIP File
      wbWIP.Activate
      Sheets("Project Summary").Range("B12:D15").Copy
    
    'Select CO File
      wb.Activate
      Sheets("Rates").Select
      Range("E3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
      Application.CutCopyMode = False
      Range("A1").Select
    
    'Close WIP File
      Workbooks(WIPName).Close SaveChanges:=False
    
    'Save and Close CO Workbook
      wb.Close SaveChanges:=True

    'Get next file name
      MyCOFile = Dir
  Loop

'Message Box when tasks are completed
  MsgBox "Task Complete!"

ResetSettings:
  'Reset Macro Optimization Settings
    Application.ScreenUpdating = True

End Sub
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Lian

New Member
Joined
Jan 26, 2021
Messages
33
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Try this:
VBA Code:
    Dim arr As Variant
    Dim brr As Variant
    'Select WIP File
    wbWIP.Activate

    arr = Sheets("Project Summary").Range("B12:D15")
    ReDim brr(1 To UBound(arr), 1 To UBound(arr, 2))
        For i = 1 To UBound(arr)
            For j = 1 To UBound(arr, 2)
                brr(i, j) = Round(arr(i, j), 2)
            
            Next
        Next
    
    'Select CO File
    wb.Activate
    
    With wb.Sheets("Rates")
        .Range("E3").Resize(UBound(arr), UBound(arr, 2)) = brr
        .Range("A1").Select
    End With
 
Solution

mjohnston0209

New Member
Joined
Nov 6, 2017
Messages
46
Try this:
VBA Code:
    Dim arr As Variant
    Dim brr As Variant
    'Select WIP File
    wbWIP.Activate

    arr = Sheets("Project Summary").Range("B12:D15")
    ReDim brr(1 To UBound(arr), 1 To UBound(arr, 2))
        For i = 1 To UBound(arr)
            For j = 1 To UBound(arr, 2)
                brr(i, j) = Round(arr(i, j), 2)
           
            Next
        Next
   
    'Select CO File
    wb.Activate
   
    With wb.Sheets("Rates")
        .Range("E3").Resize(UBound(arr), UBound(arr, 2)) = brr
        .Range("A1").Select
    End With
This works perfectly! Thanks for your help! I didn't even think to use arrays. Mainly because they are a weak point for me.

Can you explain the following lines? I am having difficulty understanding them?

VBA Code:
    ReDim brr(1 To UBound(arr), 1 To UBound(arr, 2))
    
   .Range("E3").Resize(UBound(arr), UBound(arr, 2)) = brr
 

Lian

New Member
Joined
Jan 26, 2021
Messages
33
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Usually we use arr = Range("A1:B15") (eg.) or arr = Range("A1").currentRegion to get the range into array.
Then we got a two dimension array. Ubound to get the upper limit.
Ubound(arr) or Ubound(arr,1) will help us get the rows.
Ubound(arr,2) will help us get the columns.
x=ubound(arr) 'rows y=ubound(arr,2) 'columns.
Then we know that the arr is array(x, y)

If we assign the a new array, we need redim the dimension, so we redim brr( 1 to X, 1 to Y)
Range("E3").resize(X,Y) = brr, we resize/adjust the range of Range("E3") eaqul to the dimension of brr.

Array is really a big help in vba coding especially for the calculation and data process.
 

mjohnston0209

New Member
Joined
Nov 6, 2017
Messages
46
Usually we use arr = Range("A1:B15") (eg.) or arr = Range("A1").currentRegion to get the range into array.
Then we got a two dimension array. Ubound to get the upper limit.
Ubound(arr) or Ubound(arr,1) will help us get the rows.
Ubound(arr,2) will help us get the columns.
x=ubound(arr) 'rows y=ubound(arr,2) 'columns.
Then we know that the arr is array(x, y)

If we assign the a new array, we need redim the dimension, so we redim brr( 1 to X, 1 to Y)
Range("E3").resize(X,Y) = brr, we resize/adjust the range of Range("E3") eaqul to the dimension of brr.

Array is really a big help in vba coding especially for the calculation and data process.
Thank you! That's seems relatively straight forward. I will have to practice with arrays going forward.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,802
Messages
5,626,975
Members
416,213
Latest member
neflerine

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
Top