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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Forum statistics

Threads
1,215,465
Messages
6,124,977
Members
449,200
Latest member
Jamil ahmed

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