VBA - Button saves TextBox data in dif. Workbook [Closed]

Report
Posts
6
Registration date
Friday September 21, 2018
Status
Member
Last seen
October 2, 2018
-
 Blocked Profile -
Hi Everyone,

I need some help creating a code that will allow me to save data input into a TextBox to a different Workbook called "FoundData.xlsm" - sheet "FoundData"

Workbook location = FileShare-MFG (N:)/Manufacturing/PhysicalInventory/Tools

So far I was able to use a CommandButton to save into a worksheet on the same file.

I would really appreciate any input and help.


Attached bellow it's the current VBA code.

Thanks in advance,

Private Sub cmdSave_Click()
Dim lRow As Long
Dim lPart As Long
Dim wb As Workbook
Set wb = Worksheets("FoundItems")

'find first empty row in database
lRow = wb.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

With wb
' .Unprotect Password:="password"
.Cells(lRow, 1).Value = Me.txtQuarter.Value
.Cells(lRow, 2).Value = Me.txtDate.Value
.Cells(lRow, 3).Value = Me.txtTSN.Value
.Cells(lRow, 4).Value = Me.txtPN.Value
.Cells(lRow, 5).Value = Me.txtQty.Value
.Cells(lRow, 6).Value = Me.txtLoc.Value
.Cells(lRow, 7).Value = Me.txtName.Value
.Cells(lRow, 8).Value = Me.txtComments.Value
' .Protect Password:="password"
End With

'clear the data
Me.txtDate.Value = ""
Me.txtTSN.Value = ""
Me.txtPN.Value = ""
Me.txtQty.Value = ""
Me.txtLoc.Value = ""
Me.txtComments.Value = ""

End Sub

1 reply


Its ok. Record a macro doing exactly what you want, and substitute the hardcoded items into variables.


Sub Macro1()
'
' Macro1 Macro
'

'
ChDir "C:\Users\User\Desktop"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\User\Desktop\thisisthenewbookname.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
End Sub

--
It's kind of fun to do the impossible! -Walter Elias Disney
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month