I have a macro that writes data from one worksheet to another inside the same workbook. I want to write the data to another workbook if this is possible using my code below, i was wondering could i change the first line of the code "Set wsData = Sheet2" to the pathname of the workbook and worksheet i want to write the data to, i dont think this will work though, anyone have any ideas?
Sub Save_Data()
Dim wsData As Worksheet
Dim wsForm As Worksheet
Dim rNextCl As Range
'variables
Set wsData = Sheet2
Set wsForm = Sheet1
'find the next empty row for data input
Set rNextCl = wsData.Cells(65536, 1).End(xlUp).Offset(1, 0)
With rNextCl
.Value = wsForm.Cells(2, 2).Value
.Offset(0, 1).Value = wsForm.Cells(3, 2).Value
.Offset(0, 2).Value = wsForm.Cells(4, 2).Value
.Offset(0, 3).Value = wsForm.Cells(5, 2).Value
.Offset(0, 4).Value = wsForm.Cells(6, 2).Value
End With
'clear input cells, note use of Named Range
wsForm.Range("Data").ClearContents
'clear variables
Set wsData = Nothing
Set wsForm = Nothing
Set rNextCl = Nothing
'data transfer message
MsgBox "Data transferred" & vbCrLf & "", vbInformation, "Data transfer"
End Sub
Bookmarks