Help needed on a macro please

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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Maybe the use of the Select Case routine in your code..

Select Case ws
Case = "EHA"
With Sheets("EHA")
.Cells(iRow, 3).Value = Me.ListBox1.Value
.Cells(iRow, 2).Value = Me.TextBox1.Value
.Cells(iRow, 4).Value = Me.TextBox2.Value
.Cells(iRow, 5).Value = Me.TextBox3.Value
....
End With
Case "MHA"
With Sheets("MHA")
.Cells(iRow, 3).Value = Me.ListBox1.Value
.Cells(iRow, 2).Value = Me.TextBox1.Value
.Cells(iRow, 4).Value = Me.TextBox2.Value
.Cells(iRow, 5).Value = Me.TextBox3.Value
....
End With
 
Upvote 0
ok so here is what I was thinking for workbooks
Code:
Dim LFile As String
LFile = Me.Listbox1.Value
LFile = LFile & " Remit"
Windows(LFile).ws.Cells(iRow, 3).Value = Me.ListBox1.Value
 
Upvote 0
Hello all

I currently have a macro that uses the data from a userform into a different spreadsheet within the same workbook...

...
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)

You use the term spreadsheet when refering to a sheet within a workbook and then later use the term spreadsheet as the workbook.

It's not clear if you want to use Me.ListBox1.Value to reference a different Workbook or a Sheet within the current workbook.

This uses Me.ListBox1.Value as a sheet name within the current workbook.
Code:
Dim ws As Worksheet

Set ws = Nothing
On Error Resume Next
    Set ws = Sheets(Me.ListBox1.Value)
On Error GoTo 0

If Not ws Is Nothing Then
    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
Else
    MsgBox "Couldn't locate the sheet named """ & Me.ListBox1.Value & " """
End If


This is how you can save a copy of the workbook when it is opened.
Put this in the Thisworkbook module. It will automatically run when the workbook opens. Set the path (in red) that you want to save to.
Code:
Private Sub Workbook_Open()
    
    ThisWorkbook.SaveCopyAs Filename:="[COLOR="Red"]C:\Temp\[/COLOR]" & ThisWorkbook.Name
    
End Sub
 
Upvote 0
thanks for your help. The saveas is not working at the moment but think that this is to do with our server path rather than your valuable advise.
 
Upvote 0

Forum statistics

Threads
1,224,521
Messages
6,179,275
Members
452,902
Latest member
Knuddeluff

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