Saving to a particular tab

[Hoopsah]

Hi,

I have a spreadsheet where the user enters details, these are copied in tab "Worksheet"

I would like to have a macro that when the user clicks a submit button the details will be copied into the relevant tabs by account number.

i.e. Account code 8332 the details would be copied into tab 8332

The account codes will change constantly, so it will have to check the account code first

Hope this makes sense

I'll attach a copy of what I have so far

Thanks for any help

[Bob Phillips]

Public Sub SubmitData()
Dim i As Long
Dim NextRow As Long

With Worksheets("Worksheet")

For i = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row

NextRow = Worksheets(.Cells(i, "F").Text).Cells(.Rows.Count, "A").End(xlUp).Row + 1
.Rows(i).Copy Worksheets(.Cells(i, "F").Text).Cells(NextRow, "A")
Next i
End With
End Sub

[Hoopsah]

Hi Bob,

thanks for that. However, it doesn't seem to be working right.

I have added the macro to a button on the first page, but it copies the lines into the wrong tabs??

[Bob Phillips]

LOL! It kept the formulae



Public Sub SubmitData()
Dim i As Long
Dim NextRow As Long
Dim Target As Worksheet

With Worksheets("Worksheet")

For i = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row

Set Target = Worksheets(.Cells(i, "F").Text)
NextRow = Target.Cells(.Rows.Count, "A").End(xlUp).Row + 1
.Rows(i).Copy
Target.Cells(NextRow, "A").PasteSpecial Paste:=xlPasteValues
Next i
End With
End Sub

[Hoopsah]

Magic.

Thanks Bob - Works a treat

Cheers

Gerry

[lucas]

where is the attachment? This thread makes no sense without it. Please don't delete your attachments hoopsah. You should sanitize it before posting it so it can remain posted for others to glean knowledge from.

[david000]

where is the attachment? This thread makes no sense without it.
hahaha!:p That's too funny!

I copied the code to read later in the day, and I thought gee, I better check the original post again!

[Hoopsah]

Hi Lucas

sorry about that - I get into the habit of deleting things regularly - save filling up your server.

copy of the original worksheet attached

Hoops

[lucas]

Thanks hoops, Integrity of the thread is more important than a 19kb file taking up server space.

Remember, these threads are for more than your purpose. Many learn from your projects without ever posting.

[Hoopsah]

Hi Bob,

wonder if you can help me here - I have been messing about with the original file and now I don't know what I've done, but when I try and run the Macro to Submit the details to master I keep getting an error.

I can't see anything obvious

:dunno

[Bob Phillips]

It looks as though you having been re-ordering the columns of the Worksheet tab Gerry.

I think you just need to change this line



Set Target = Worksheets(.Cells(i, "F").Text)

to this



Set Target = Worksheets(.Cells(i, "J").Text)

[Hoopsah]

Phew!!!

Thanks Bob - I had been doing that much messing that I had sort of forgotten when it last was working ok.

Thanks again Bob

Gerry