Writing a macro to capture values on one spreadsheet and add them to totals on another spreadsheet that is out on the server.

ghostbroker2

Board Regular
Joined
Feb 13, 2017
Messages
58
Production Sheet dropbox link:
https://www.dropbox.com/s/vi2zpdo86euia3d/Production%20sheet%20%28TEST%29.xlsm?dl=0
Extrusion Tracking Dropbox link:
https://www.dropbox.com/s/5rn7wcsy62bupcl/Extrusion%20Tracking%20TEST.xlsx?dl=0
On the Production Sheet, cells K3 through K5 will contain scrap values. Cells I3 through I5 contain operators names in drop down menus.
I need totals from K3:K5 combined with values in column P on the Extrusion Tracking sheet. I need the total to go to the row with the corresponding name from the Production Sheet cells I3:I5.
The Extrusion tracking spreadsheet lives out on our server and is usually not open. The pathway is:
"\\RWCAD\Dashboards\Extrusion Tracking TEST.xlxs"
I'm still new to VBA and don't know how to pull this one off.
I've tried to modify a macro that you guys helped write for me, but no avail.
Here's what I tried, but not working.

Sub macSubmitScrap()
Dim W, wbSource As Workbook
Dim C As Range
Dim lngR As Long

Set wbSource = ActiveWorkbook
Set W = Application.Workbooks.Open("\\RWCAD\Dashboards\Extrusion Tracking TEST.xlsx")
Set C = W.Sheets(1).Range("B4:B15").Find(wbSource.Sheets(1).Range("I3").Value)

If C Is Nothing Then
MsgBox wbSource.Sheets(1).Range("I3").Value & " Was Not Found!"
Exit Sub
End If

lngR = C.Row

With W.Sheets(1).Cells(lngR, "P")
.Value = .Value + wbSource.Sheets(1).Range("K3").Value
End With
With W.Sheets(1).Cells(lngR, "P")
.Value = .Value + wbSource.Sheets(1).Range("K4").Value
End With
With W.Sheets(1).Cells(lngR, "P")
.Value = .Value + wbSource.Sheets(1).Range("K4").Value
End With

W.Close True

End Sub
 
Last edited:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Forum statistics

Threads
1,215,949
Messages
6,127,877
Members
449,410
Latest member
adunn_23

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