mjohnston0209
Board Regular
- Joined
- Nov 6, 2017
- Messages
- 55
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.
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