Currently I use a macro to highlight the selected row on a Price List and
then a 2nd macro (different macros for pricing levels) to paste selected info
from that row to another worksheet(Quote). I would like to be able to select
multiple items and add them all in on go. I am a bit of a novice and would
appreciate some help.
Thanks
--
AJM1949

are you copying the entire row to the other sheet, or just a certain number
of columns from the rows you've selected. if you're copying the entire row,
you may be able to use this macro, which copies the selected rows to column A
of another worksheet, and below the usedrange of the worksheet.

Sub CopyRows()
Dim WkSht As Worksheet
Set WkSht = Sheets("Quote")

If IsEmpty(WkSht.UsedRange) Then
Selection.EntireRow.Copy WkSht.Cells(1, 1)
Else: Selection.EntireRow.Copy WkSht.Cells _
(WkSht.UsedRange.Rows.Count + 1, 1)
End If

End Sub

"AJM1949" wrote:

Currently I use a macro to highlight the selected row on a Price List and
then a 2nd macro (different macros for pricing levels) to paste selected info
from that row to another worksheet(Quote). I would like to be able to select
multiple items and add them all in on go. I am a bit of a novice and would
appreciate some help.
Thanks
--
AJM1949

Thanks for the reply. I can use that macro for something else. However I copy
only specific columns and not necessarily to the same column on the quote
worksheet. This is an example of the macro used.
Sub Platinum()
'
' Platinum Macro
' Macro recorded 7/03/2002 by Alan McCrudden
'
'Add vehicle to Calculator
r$ = Trim(Str(ActiveCell.Row))
Counter = 4
Do While Not Range("ToyotaQM.xls!A" & Counter).Value = ""
Counter = Counter + 1
Loop

If Counter <= 19 Then
Range("ToyotaQM.xls!A" & Counter).Value = Range("a" + r$) 'Model
Range("ToyotaQM.xls!B" & Counter).Value = Range("b" + r$) 'Description
Range("ToyotaQM.xls!c" & Counter).Value = Range("C" + r$) 'Description
Range("ToyotaQM.xls!D" & Counter).Value = Range("L" + r$) - Range("R" +
r$) 'RRP less LCT
Range("ToyotaQM.xls!H" & Counter).Value = Range("Z" + r$) 'Holdback
Range("ToyotaQM.xls!G" & Counter).Value = Range("O" + r$) 'Margin
Range("ToyotaQM.xls!K" & Counter).Value = Range("P" + r$) 'Platinum Fleet
Range("ToyotaQM.xls!FleetType").Value = "Platinum Fleet"
Else
MsgBox "Too Many Vehicles/Options", vbExclamation, "Quotemaster"
End If

End Sub
I hope this clarifies my needs
Thanks again for your help.
--
AJM1949


"JMB" wrote:

are you copying the entire row to the other sheet, or just a certain number
of columns from the rows you've selected. if you're copying the entire row,
you may be able to use this macro, which copies the selected rows to column A
of another worksheet, and below the usedrange of the worksheet.

Sub CopyRows()
Dim WkSht As Worksheet
Set WkSht = Sheets("Quote")

If IsEmpty(WkSht.UsedRange) Then
Selection.EntireRow.Copy WkSht.Cells(1, 1)
Else: Selection.EntireRow.Copy WkSht.Cells _
(WkSht.UsedRange.Rows.Count + 1, 1)
End If

End Sub

"AJM1949" wrote:

Currently I use a macro to highlight the selected row on a Price List and
then a 2nd macro (different macros for pricing levels) to paste selected info
from that row to another worksheet(Quote). I would like to be able to select
multiple items and add them all in on go. I am a bit of a novice and would
appreciate some help.
Thanks
--
AJM1949

maybe something more like this? since the columns on your source sheet and
target sheet don't match up, i think you'll have to copy one column at a
time. But I also see you are copying the values, so I assume you have
formulas in your source and want the values hardcoded into the target.


Dim WkSht As Worksheet
Dim Counter As Long
Dim FirstRow As Long

Set WkSht = Sheets("ToyotaQM.xls")
FirstRow = Selection.Cells(1, 1).Row

<your code to initialize counter

If Counter <= 19 Then
For i = 1 To Selection.Rows.Count
WkSht.Cells(Counter + i - 1, 1).Value = _
ActiveSheet.Cells(FirstRow + i - 1, 1).Value

<code to copy the rest of the columns

Next i
End If


the syntax is Cells(Row, Column). you should be able to copy the above copy
statement for the rest of your columns and just change the column numbers on
your source and target ranges. also, you may need to change your test to

If Counter + Selection.Rows.Count - 1 <= 19

since you will be copying multiple rows at a time.

hope this helps. of course, back up your data.


"AJM1949" wrote:

Thanks for the reply. I can use that macro for something else. However I copy
only specific columns and not necessarily to the same column on the quote
worksheet. This is an example of the macro used.
Sub Platinum()
'
' Platinum Macro
' Macro recorded 7/03/2002 by Alan McCrudden
'
'Add vehicle to Calculator
r$ = Trim(Str(ActiveCell.Row))
Counter = 4
Do While Not Range("ToyotaQM.xls!A" & Counter).Value = ""
Counter = Counter + 1
Loop

If Counter <= 19 Then
Range("ToyotaQM.xls!A" & Counter).Value = Range("a" + r$) 'Model
Range("ToyotaQM.xls!B" & Counter).Value = Range("b" + r$) 'Description
Range("ToyotaQM.xls!c" & Counter).Value = Range("C" + r$) 'Description
Range("ToyotaQM.xls!D" & Counter).Value = Range("L" + r$) - Range("R" +
r$) 'RRP less LCT
Range("ToyotaQM.xls!H" & Counter).Value = Range("Z" + r$) 'Holdback
Range("ToyotaQM.xls!G" & Counter).Value = Range("O" + r$) 'Margin
Range("ToyotaQM.xls!K" & Counter).Value = Range("P" + r$) 'Platinum Fleet
Range("ToyotaQM.xls!FleetType").Value = "Platinum Fleet"
Else
MsgBox "Too Many Vehicles/Options", vbExclamation, "Quotemaster"
End If

End Sub
I hope this clarifies my needs
Thanks again for your help.
--
AJM1949


"JMB" wrote:

are you copying the entire row to the other sheet, or just a certain number
of columns from the rows you've selected. if you're copying the entire row,
you may be able to use this macro, which copies the selected rows to column A
of another worksheet, and below the usedrange of the worksheet.

Sub CopyRows()
Dim WkSht As Worksheet
Set WkSht = Sheets("Quote")

If IsEmpty(WkSht.UsedRange) Then
Selection.EntireRow.Copy WkSht.Cells(1, 1)
Else: Selection.EntireRow.Copy WkSht.Cells _
(WkSht.UsedRange.Rows.Count + 1, 1)
End If

End Sub

"AJM1949" wrote:

Currently I use a macro to highlight the selected row on a Price List and
then a 2nd macro (different macros for pricing levels) to paste selected info
from that row to another worksheet(Quote). I would like to be able to select
multiple items and add them all in on go. I am a bit of a novice and would
appreciate some help.
Thanks
--
AJM1949

many thanks for your help. Sorry I am a bit slow to reply-got sidetracked
with something else. The code works well when I select multiple rows by
holding the Shift Key down(great for when the items are directly under one
another-unfortunately a rare requirement), but only takes the 1st row when I
select multiple rows using the Crtl key. Any ideas on how to overcome this?
--
AJM1949


"JMB" wrote:

maybe something more like this? since the columns on your source sheet and
target sheet don't match up, i think you'll have to copy one column at a
time. But I also see you are copying the values, so I assume you have
formulas in your source and want the values hardcoded into the target.


Dim WkSht As Worksheet
Dim Counter As Long
Dim FirstRow As Long

Set WkSht = Sheets("ToyotaQM.xls")
FirstRow = Selection.Cells(1, 1).Row

<your code to initialize counter

If Counter <= 19 Then
For i = 1 To Selection.Rows.Count
WkSht.Cells(Counter + i - 1, 1).Value = _
ActiveSheet.Cells(FirstRow + i - 1, 1).Value

<code to copy the rest of the columns

Next i
End If


the syntax is Cells(Row, Column). you should be able to copy the above copy
statement for the rest of your columns and just change the column numbers on
your source and target ranges. also, you may need to change your test to

If Counter + Selection.Rows.Count - 1 <= 19

since you will be copying multiple rows at a time.

hope this helps. of course, back up your data.


"AJM1949" wrote:

Thanks for the reply. I can use that macro for something else. However I copy
only specific columns and not necessarily to the same column on the quote
worksheet. This is an example of the macro used.
Sub Platinum()
'
' Platinum Macro
' Macro recorded 7/03/2002 by Alan McCrudden
'
'Add vehicle to Calculator
r$ = Trim(Str(ActiveCell.Row))
Counter = 4
Do While Not Range("ToyotaQM.xls!A" & Counter).Value = ""
Counter = Counter + 1
Loop

If Counter <= 19 Then
Range("ToyotaQM.xls!A" & Counter).Value = Range("a" + r$) 'Model
Range("ToyotaQM.xls!B" & Counter).Value = Range("b" + r$) 'Description
Range("ToyotaQM.xls!c" & Counter).Value = Range("C" + r$) 'Description
Range("ToyotaQM.xls!D" & Counter).Value = Range("L" + r$) - Range("R" +
r$) 'RRP less LCT
Range("ToyotaQM.xls!H" & Counter).Value = Range("Z" + r$) 'Holdback
Range("ToyotaQM.xls!G" & Counter).Value = Range("O" + r$) 'Margin
Range("ToyotaQM.xls!K" & Counter).Value = Range("P" + r$) 'Platinum Fleet
Range("ToyotaQM.xls!FleetType").Value = "Platinum Fleet"
Else
MsgBox "Too Many Vehicles/Options", vbExclamation, "Quotemaster"
End If

End Sub
I hope this clarifies my needs
Thanks again for your help.
--
AJM1949


"JMB" wrote:

are you copying the entire row to the other sheet, or just a certain number
of columns from the rows you've selected. if you're copying the entire row,
you may be able to use this macro, which copies the selected rows to column A
of another worksheet, and below the usedrange of the worksheet.

Sub CopyRows()
Dim WkSht As Worksheet
Set WkSht = Sheets("Quote")

If IsEmpty(WkSht.UsedRange) Then
Selection.EntireRow.Copy WkSht.Cells(1, 1)
Else: Selection.EntireRow.Copy WkSht.Cells _
(WkSht.UsedRange.Rows.Count + 1, 1)
End If

End Sub

"AJM1949" wrote:

Currently I use a macro to highlight the selected row on a Price List and
then a 2nd macro (different macros for pricing levels) to paste selected info
from that row to another worksheet(Quote). I would like to be able to select
multiple items and add them all in on go. I am a bit of a novice and would
appreciate some help.
Thanks
--
AJM1949

I had not counted on selecting multiple areas. I changed it to use a For
Each loop instead. Also, I included a variable, RowCount, to identify how
many rows are selected so that, if you need to, you can use it to determine
if the selected rows + counter <= 19.



Sub CopyData()

Dim WkSht As Worksheet
Dim Counter As Long
Dim RowCount As Long
Dim i As Object, y As Object

Set WkSht = Sheets("ToyotaQM.xls")

' <your code to initialize counter

For Each i In Selection.Areas
RowCount = RowCount + i.Rows.Count
Next i

If Counter <= 19 Then ' Maybe S/B Counter + RowCount <= 19 ??????
For Each y In Selection
WkSht.Cells(Counter, 1).Value = y.Value
WkSht.Cells(Counter, 2).Value = _
Cells(y.Row, y.Column + 1).Value
WkSht.Cells(Counter, 3).Value = _
Cells(y.Row, y.Column + 2).Value
WkSht.Cells(Counter, 4).Value = _
Cells(y.Row, y.Column + 11).Value - _
Cells(y.Row, y.Column + 17).Value

WkSht.Cells(Counter, 8).Value = _
Cells(y.Row, y.Column + 25).Value
WkSht.Cells(Counter, 7).Value = _
Cells(y.Row, y.Column + 14).Value
WkSht.Cells(Counter, 11).Value = _
Cells(y.Row, y.Column + 15).Value

Counter = Counter + 1
Next y

Range("ToyotaQM.xls!FleetType").Value = "Platinum Fleet"
End If

End Sub


"AJM1949" wrote:

many thanks for your help. Sorry I am a bit slow to reply-got sidetracked
with something else. The code works well when I select multiple rows by
holding the Shift Key down(great for when the items are directly under one
another-unfortunately a rare requirement), but only takes the 1st row when I
select multiple rows using the Crtl key. Any ideas on how to overcome this?
--
AJM1949


"JMB" wrote:

maybe something more like this? since the columns on your source sheet and
target sheet don't match up, i think you'll have to copy one column at a
time. But I also see you are copying the values, so I assume you have
formulas in your source and want the values hardcoded into the target.


Dim WkSht As Worksheet
Dim Counter As Long
Dim FirstRow As Long

Set WkSht = Sheets("ToyotaQM.xls")
FirstRow = Selection.Cells(1, 1).Row

<your code to initialize counter

If Counter <= 19 Then
For i = 1 To Selection.Rows.Count
WkSht.Cells(Counter + i - 1, 1).Value = _
ActiveSheet.Cells(FirstRow + i - 1, 1).Value

<code to copy the rest of the columns

Next i
End If


the syntax is Cells(Row, Column). you should be able to copy the above copy
statement for the rest of your columns and just change the column numbers on
your source and target ranges. also, you may need to change your test to

If Counter + Selection.Rows.Count - 1 <= 19

since you will be copying multiple rows at a time.

hope this helps. of course, back up your data.


"AJM1949" wrote:

Thanks for the reply. I can use that macro for something else. However I copy
only specific columns and not necessarily to the same column on the quote
worksheet. This is an example of the macro used.
Sub Platinum()
'
' Platinum Macro
' Macro recorded 7/03/2002 by Alan McCrudden
'
'Add vehicle to Calculator
r$ = Trim(Str(ActiveCell.Row))
Counter = 4
Do While Not Range("ToyotaQM.xls!A" & Counter).Value = ""
Counter = Counter + 1
Loop

If Counter <= 19 Then
Range("ToyotaQM.xls!A" & Counter).Value = Range("a" + r$) 'Model
Range("ToyotaQM.xls!B" & Counter).Value = Range("b" + r$) 'Description
Range("ToyotaQM.xls!c" & Counter).Value = Range("C" + r$) 'Description
Range("ToyotaQM.xls!D" & Counter).Value = Range("L" + r$) - Range("R" +
r$) 'RRP less LCT
Range("ToyotaQM.xls!H" & Counter).Value = Range("Z" + r$) 'Holdback
Range("ToyotaQM.xls!G" & Counter).Value = Range("O" + r$) 'Margin
Range("ToyotaQM.xls!K" & Counter).Value = Range("P" + r$) 'Platinum Fleet
Range("ToyotaQM.xls!FleetType").Value = "Platinum Fleet"
Else
MsgBox "Too Many Vehicles/Options", vbExclamation, "Quotemaster"
End If

End Sub
I hope this clarifies my needs
Thanks again for your help.
--
AJM1949


"JMB" wrote:

are you copying the entire row to the other sheet, or just a certain number
of columns from the rows you've selected. if you're copying the entire row,
you may be able to use this macro, which copies the selected rows to column A
of another worksheet, and below the usedrange of the worksheet.

Sub CopyRows()
Dim WkSht As Worksheet
Set WkSht = Sheets("Quote")

If IsEmpty(WkSht.UsedRange) Then
Selection.EntireRow.Copy WkSht.Cells(1, 1)
Else: Selection.EntireRow.Copy WkSht.Cells _
(WkSht.UsedRange.Rows.Count + 1, 1)
End If

End Sub

"AJM1949" wrote:

Currently I use a macro to highlight the selected row on a Price List and
then a 2nd macro (different macros for pricing levels) to paste selected info
from that row to another worksheet(Quote). I would like to be able to select
multiple items and add them all in on go. I am a bit of a novice and would
appreciate some help.
Thanks
--
AJM1949