andrewhoddie
Board Regular
- Joined
- Dec 21, 2008
- Messages
- 118
Hello all
I currently have a macro that uses the data from a userform into a different spreadsheet within the same workbook using
ws.Cells(iRow, 3).Value = Me.ListBox1.Value
ws.Cells(iRow, 2).Value = Me.TextBox1.Value
ws.Cells(iRow, 4).Value = Me.TextBox2.Value
ws.Cells(iRow, 5).Value = Me.TextBox3.Value
ws.Cells(iRow, 6).Value = Me.TextBox4.Value
ws.Cells(iRow, 7).Value = Me.TextBox5.Value
ws.Cells(iRow, 8).Value = Me.TextBox6.Value
ws.Cells(iRow, 9).Value = Me.TextBox7.Value
ws.Cells(iRow, 10).Value = Me.TextBox8.Value
ws.Cells(iRow, 11).Value = Me.TextBox9.Value
ws.Cells(iRow, 12).Value = Me.TextBox10.Value
ws.Cells(iRow, 14).Value = Me.TextBox11.Value
ws.Cells(iRow, 13).Value = Me.ListBox2.Value
Is there a way of using the data in listbox1 to specify which spreadsheet it posts too e.g.
If listbox1 = EHA then it goes to spreadsheet named EHA Remit
if listbox1 = MHA then it goes to spreadsheet named MHA Remit
if listbox1 = LDHA then it goes to spreadsheet named LDA Remit
If listbox1 = AA then it goes to spreadsheet names AA Remit
Also is there a way that everytime this spreadsheet is opened it saves a copy in another location (it can override the existing file or not whichever is possible)
Thanks in advance for your help
Andrew
I currently have a macro that uses the data from a userform into a different spreadsheet within the same workbook using
ws.Cells(iRow, 3).Value = Me.ListBox1.Value
ws.Cells(iRow, 2).Value = Me.TextBox1.Value
ws.Cells(iRow, 4).Value = Me.TextBox2.Value
ws.Cells(iRow, 5).Value = Me.TextBox3.Value
ws.Cells(iRow, 6).Value = Me.TextBox4.Value
ws.Cells(iRow, 7).Value = Me.TextBox5.Value
ws.Cells(iRow, 8).Value = Me.TextBox6.Value
ws.Cells(iRow, 9).Value = Me.TextBox7.Value
ws.Cells(iRow, 10).Value = Me.TextBox8.Value
ws.Cells(iRow, 11).Value = Me.TextBox9.Value
ws.Cells(iRow, 12).Value = Me.TextBox10.Value
ws.Cells(iRow, 14).Value = Me.TextBox11.Value
ws.Cells(iRow, 13).Value = Me.ListBox2.Value
Is there a way of using the data in listbox1 to specify which spreadsheet it posts too e.g.
If listbox1 = EHA then it goes to spreadsheet named EHA Remit
if listbox1 = MHA then it goes to spreadsheet named MHA Remit
if listbox1 = LDHA then it goes to spreadsheet named LDA Remit
If listbox1 = AA then it goes to spreadsheet names AA Remit
Also is there a way that everytime this spreadsheet is opened it saves a copy in another location (it can override the existing file or not whichever is possible)
Thanks in advance for your help
Andrew