macro question
 

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

macro question
 

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

macro question
 

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

macro question
 

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!

macro question
 

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.

macro question
 

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.

macro question
 

"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

macro question
 

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!

macro question
 

Scott,

Looks familiar<vbg

What is not working? I ran it and it seems to work. What happens is that is
a change is made in any row of column 2, it is checked for one of those
currencies. If it matches, it formats columns F,G,J,K and M in the
prescribed format. What does it not do that it should?

On the other sheet, you only want the event to trigger for rows 9-12 & 23-28
for columns 6,7,10,& 11? Same currency tests?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"scott jeremy" wrote in message
...


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!

macro question
 

Bob,
I think my mistake was that i was assuming '5' was column E and so on...
but i guess it takes the event column and uses that as the start and
thus column E is really 4 since the event column was 2 ? (My
understanding ?)

At any rate, yes now i understand how to get it correct on that sheet.

On the 2nd sheet, yes you are correct i only want the row event to be
triggered by column C rows 9-12 and 23-28.
Thanks again.
scott



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

macro question
 

Scott,

You're right, 5 is 5 columns beyond the target column.

Here's the code for the other sheet

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
With Target
If .Column = 3 Then
If ((.Row = 9 And .Row <= 12) Or _
(.Row 23 And .Row <= 28)) 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
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub FormatCells(rng As Range, format As String)
rng.Cells(1, 3).NumberFormat = format
rng.Cells(1, 4).NumberFormat = format
rng.Cells(1, 7).NumberFormat = format
rng.Cells(1, 8).NumberFormat = format
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"scott jeremy" wrote in message
...
Bob,
I think my mistake was that i was assuming '5' was column E and so on...
but i guess it takes the event column and uses that as the start and
thus column E is really 4 since the event column was 2 ? (My
understanding ?)

At any rate, yes now i understand how to get it correct on that sheet.

On the 2nd sheet, yes you are correct i only want the row event to be
triggered by column C rows 9-12 and 23-28.
Thanks again.
scott



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