Use VBA to create a formula to a different Workbook

[mjohnston0209]

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.

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

 

[Lian]

Try this:

    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

 

[mjohnston0209]

Try this:

    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?

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

 

[Lian]

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]

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.