Im trying to write a macro that will take a row of data in one
worksheet and copy it into a second worksheet (same workbook) within a
table. The part i cant figure out is how to code the macro so that it
will know to paste the row of data into the first available blank row
at the bottom of the table.

Im basically trying to allow one worksheet as the data entry and open
position sheet. However, once a button is clicked executing a macro,
it will take this row of data entry and move it to the second
worksheet which contains the closed position(historical) data. I just
cant figure out how to tell it to put it at the end, or immediately
following the last row entered.

Thanks if you can help.
sg

Scott,

What column are you trying to enter it in???

range("A" & rows.Count).End(xlUp).row + 1

will give you the last blank row in Column "A"
You'll need to specify which worksheet you want the last row of
or insure that it's active when you run the above code.

Worksheets("mysheetname").range("A" & rows.Count).End(xlUp).row + 1

John

"scott23" wrote in message
om...
Im trying to write a macro that will take a row of data in one
worksheet and copy it into a second worksheet (same workbook) within a
table. The part i cant figure out is how to code the macro so that it
will know to paste the row of data into the first available blank row
at the bottom of the table.

Im basically trying to allow one worksheet as the data entry and open
position sheet. However, once a button is clicked executing a macro,
it will take this row of data entry and move it to the second
worksheet which contains the closed position(historical) data. I just
cant figure out how to tell it to put it at the end, or immediately
following the last row entered.

Thanks if you can help.
sg

sg

This macro will copy row 1 of active sheet to first available row below the
data in Sheet2.

Sub Copyit()
Range("1:1").Copy Destination:= _
Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
End Sub

Basically, it takes the copied row 1 and scans Sheet2 from the bottom up and
pastes it below last row.

Gord Dibben Excel MVP

On 12 Jan 2004 21:03:34 -0800, (scott23) wrote:

Im trying to write a macro that will take a row of data in one
worksheet and copy it into a second worksheet (same workbook) within a
table. The part i cant figure out is how to code the macro so that it
will know to paste the row of data into the first available blank row
at the bottom of the table.

Im basically trying to allow one worksheet as the data entry and open
position sheet. However, once a button is clicked executing a macro,
it will take this row of data entry and move it to the second
worksheet which contains the closed position(historical) data. I just
cant figure out how to tell it to put it at the end, or immediately
following the last row entered.

Thanks if you can help.
sg

John,
Actually i dont want it to go to the last blank row. What i was hoping
for is to find the first row that is blank so that it pastes in
descending order just below the last row that already has data.


Im new to macros... would i take the code you showed me and just create
a macro that starts :
Sub 'macroname()

-
-code
-
end sub

??

Thanks john



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Scott,

"scott jeremy" wrote in message
...

John,
Actually i dont want it to go to the last blank row. What i was hoping
for is to find the first row that is blank so that it pastes in
descending order just below the last row that already has data.


John's code actually gives you the first blank row after your data, not the
last.



Im new to macros... would i take the code you showed me and just create
a macro that starts :
Sub 'macroname()

-
-code
-
end sub

Yes, or include it in the existing macro.

Bob,

I can see that my reply could have been worded a little better.

Please note the use of "Rows.Count" in my reply.
65535 to go <vbg

John

"Bob Phillips" wrote in message
...
Scott,

"scott jeremy" wrote in message
...

John,
Actually i dont want it to go to the last blank row. What i was hoping
for is to find the first row that is blank so that it pastes in
descending order just below the last row that already has data.


John's code actually gives you the first blank row after your data, not
the
last.



Im new to macros... would i take the code you showed me and just create
a macro that starts :
Sub 'macroname()

-
-code
-
end sub

Yes, or include it in the existing macro.

"John Wilson" wrote in message
...
Bob,

I can see that my reply could have been worded a little better.


But if the OP had just tried it, he would have seen that for himself<G


Please note the use of "Rows.Count" in my reply.
65535 to go <vbg


I did, and I smiled!

Bob

Thanks guys. I really really appreciate it.
It works perfectly.


Can i pick your collective brains 1 more time on a different issue
please :-)

I have a worksheet event change macro that is helping me format data
based on the change to a column.
Since i got help on this macro i changed a lot and it is not working
now. Additionally i was hoping that i can refer to multiple ranges
rather than an entire column to effect a 'change'.

Here is what i currently have:
----------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Target.Column = 2 Then
Select Case Target.Value
Case "ES": FormatCells Target, "###0.00"
Case "NQ": FormatCells Target, "###0.00"
Case "ER2": FormatCells Target, "###0.00"
Case "YM": FormatCells Target, "###0.00"
Case "ZB": FormatCells Target, "# ??/32"
Case "EUR": FormatCells Target, "0.0000"
Case "JPY": FormatCells Target, "0.00"
Case "ED": FormatCells Target, "00.000"
End Select
End If

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub FormatCells(rng As Range, format As String)

rng.Cells(1, 5).NumberFormat = format
rng.Cells(1, 6).NumberFormat = format
rng.Cells(1, 9).NumberFormat = format
rng.Cells(1, 10).NumberFormat = format
rng.Cells(1, 12).NumberFormat = format
End Sub

------------------------
I only want columns 5,6,9,10,&12 to be formatted in the same row based
on the format lists at the bottom of this macro. For some reason its
not working now.

Additionally, i copied this worksheet and am trying to do the same thing
in another worksheet , BUT instead of just assuming any change in column
2 will be a change event, i actually only want any change in column 3
rows 9-12 & 23-28 to be the catalyst.
In that case only 6,7,10,& 11 would be changed in that row to the
appropriate format.

I know it sounds like a lot but i think im just a few lines away in
both.
Thanks
scott






*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!