macro question

  • Thread starter Thread starter scott23
  • Start date Start date
S

scott23

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
 
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
 
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
 
Scott,

scott jeremy said:
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.
Click to expand...

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
Click to expand...

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
 
John Wilson said:
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>
Click to expand...

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
 
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)
 
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
 
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)
 
Back
Top