On Tue, 12 Oct 2004 19:33:58 +0100, "Bob Phillips"
wrote:

Peter,

I think it is because you are using the Selection, not where you stored the
data.

Try this

Private Sub CommandButton1_Click()

Dim rng As Range

Set rng = Cells(Rows.Count, "A").End(xlUp)(2)
rng.Value = TextBox1
rng.Copy
Sheets("Form master (2)").Range("B3").PasteSpecial Paste:=xlValues

Set rng = Sheets("Main").Cells(Rows.Count, "B").End(xlUp)(2)
rng.Value = ComboBox1
rng.Copy
Sheets("Form master (2)").Range("B7").PasteSpecial Paste:=xlValues

Set rng = Sheets("Main").Cells(Rows.Count, "C").End(xlUp)(2)
rng.Value = ComboBox2
rng.Copy
Sheets("Form master (2)").Range("B11").PasteSpecial Paste:=xlValues

Sheets("Form master (2)").Name = (TextBox1.Text)

End Sub


Hi Bob,

Thanks very much for your reply - again it worked. But it only works
sometimes!

I am writing a spreadsheet for work which contins all my questions,
but I have a small spreadsheet that I try the various bits out in.

The following script works well - does exactly what I want

Dim rng As Range
Set rng = Cells(Rows.Count, "A").End(xlUp)(2)
rng.Value = TextBox1
rng.copy
Sheets("Form master (2)").Range("B3").PasteSpecial Paste:=xlValues
Sheets("Main").Select
ActiveCell.Value = CDbl(TextBox1.Text)

However, when I put the code into my work spreadsheet I get the
following error

Run time error '9'
Subscript out of range

The script is as follows:

Dim rng As Range
'Sets AIR number
Set rng = Cells(Rows.Count, "A").End(xlUp)(2)
rng.Value = TextBoxAIR
'Next copies the contents into A1 in formmaster worksheet
rng.Copy
Sheets("Form master(2)").Range("A1").PasteSpecial Paste:=xlValues
Sheets("Bude").Select
ActiveCell.Value = CDbl(TextBoxAIR.Text)

The line that is causing the problem and is highlighted is

Sheets("Form master(2)").Range("A1").PasteSpecial Paste:=xlValues

Grateful if you could suggest what is wrong


--
Cheers

Peter

Remove the INVALID to reply