PDA

View Full Version : Writing a macro to capture values on a spreadsheet and add them to totals on another



ghostbroker2
08-31-2017, 02:35 PM
Unfortunately I cannot post the dropbox links. I'm still too new to the site.

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 in column B 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.

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("K5").Value
End With


W.Close True


End Sub
I think I may be in over my head. Any assistance is appreciated.

SamT
08-31-2017, 06:54 PM
You can leave the "http//:" off the link and post that.

You can "Go Advanced" and use the "Manage Attachments" button at the bottom to upload the Workbook here.

ghostbroker2
09-01-2017, 07:24 AM
You can leave the "http//:" off the link and post that.

You can "Go Advanced" and use the "Manage Attachments" button at the bottom to upload the Workbook here.

Production Sheet
/s/vi2zpdo86euia3d/Production%20sheet%20%28TEST%29.xlsm?dl=0

Extrusion Tracking
/s/5rn7wcsy62bupcl/Extrusion%20Tracking%20TEST.xlsx?dl=0

Had to take off https and dropbox.com

The second workbook didn't upload correctly.