Hello,

I am basically trying to write a macro in excel, it should be quite
simple really, all i am trying to is copy a range from one spreadsheet
to another, but need the range dropping down into a new row each time,
basically i have a standard form with drop down lists, so a user
accesses the form and fills it in, at the bottom of the form i have a
range which copies the data i need to populate my log on another tab,
basically the log just needs to be updated each time the form is used,
i have written a short macro, and added a button on the form to write
the new data accross, but cant get the data to appear on a new row each
time without manually having to point to a blank row first, i have
included the macro below, and would appreciate any help

Range("A64:J64").Select
Selection.Copy
Sheets("Tab 2").Select
activeCells = xllastrow + 1 + 1
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

Dim iRow As Long

With Sheets("Tab 2")
iRow = .Cells(.Rows.Count, "A").End(xlUp).Row
If iRow 1 Or .Range("A1").Value < "" Then
iRow = iRow + 1
End If

ActiveSheet.Range("A64:J64").Copy .Cells(iRow, "A")
End With


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
ps.com...
Hello,

I am basically trying to write a macro in excel, it should be quite
simple really, all i am trying to is copy a range from one spreadsheet
to another, but need the range dropping down into a new row each time,
basically i have a standard form with drop down lists, so a user
accesses the form and fills it in, at the bottom of the form i have a
range which copies the data i need to populate my log on another tab,
basically the log just needs to be updated each time the form is used,
i have written a short macro, and added a button on the form to write
the new data accross, but cant get the data to appear on a new row each
time without manually having to point to a blank row first, i have
included the macro below, and would appreciate any help

Range("A64:J64").Select
Selection.Copy
Sheets("Tab 2").Select
activeCells = xllastrow + 1 + 1
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

Give this a try,

Sub Move_Data()
'will take what's in the active sheet range A64:J64
'and put the values in sheet Tab 2 in the next available row
'starting in A2
nrow = Sheets("Tab 2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheets("Tab 2").Range("A" & nrow & ":J" & nrow) = _
ActiveSheet.Range("A64:J64").Value
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

wrote in message
ps.com...
Hello,

I am basically trying to write a macro in excel, it should be quite
simple really, all i am trying to is copy a range from one spreadsheet
to another, but need the range dropping down into a new row each time,
basically i have a standard form with drop down lists, so a user
accesses the form and fills it in, at the bottom of the form i have a
range which copies the data i need to populate my log on another tab,
basically the log just needs to be updated each time the form is used,
i have written a short macro, and added a button on the form to write
the new data accross, but cant get the data to appear on a new row each
time without manually having to point to a blank row first, i have
included the macro below, and would appreciate any help

Range("A64:J64").Select
Selection.Copy
Sheets("Tab 2").Select
activeCells = xllastrow + 1 + 1
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

Thanks so much guys, i took what Paul had wrote and it is working like
a dream, so many thanks, by the way is there an easy way to put a
button on a sheet to save and print at the same time, and also can you
hide the toolbars and stop anyone from editing the macro?

Many Thanks

Zed
Paul B wrote:
Give this a try,

Sub Move_Data()
'will take what's in the active sheet range A64:J64
'and put the values in sheet Tab 2 in the next available row
'starting in A2
nrow = Sheets("Tab 2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheets("Tab 2").Range("A" & nrow & ":J" & nrow) = _
ActiveSheet.Range("A64:J64").Value
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

wrote in message
ps.com...
Hello,

I am basically trying to write a macro in excel, it should be quite
simple really, all i am trying to is copy a range from one spreadsheet
to another, but need the range dropping down into a new row each time,
basically i have a standard form with drop down lists, so a user
accesses the form and fills it in, at the bottom of the form i have a
range which copies the data i need to populate my log on another tab,
basically the log just needs to be updated each time the form is used,
i have written a short macro, and added a button on the form to write
the new data accross, but cant get the data to appear on a new row each
time without manually having to point to a blank row first, i have
included the macro below, and would appreciate any help

Range("A64:J64").Select
Selection.Copy
Sheets("Tab 2").Select
activeCells = xllastrow + 1 + 1
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub