Paste Multiple items

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

Many Thanks JMB-Your code has helped me enormously. I have a number of
variations on the sample macro I posted and will be able to modify your code
to suit.

One interesting thing that is different from what I have been using-with my
original code it didn't matter which column was selected on the source sheet.
It appears that your code needs Column A to be selected on the source sheet.
This is NOT a problem for me.

Once again many thanks for your assistance.
--
AJM1949


"JMB" wrote:

> 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

you're welcome. sorry it took a couple of tries. yes, the macro copies the
data relative to the column that is selected (another assumption on my part).
if you ever want to play around w/it, you could make it copy the correct
rows no matter what column is selected on the source sheet

Current Code: Cells(y.Row, y.Column + 1).Value

The y.column+1 specifies the first column to the right of whatever column is
selected (which will be column B-and makes this a relative reference). You
could hardcode the column numbers

Cells(y.Row, 2).Value

which would hardcode the column numbers the way your original macro did.

y.value

would have to be changed to

Cells(y.row, 1).Value

For Column A.




"AJM1949" wrote:

> Many Thanks JMB-Your code has helped me enormously. I have a number of
> variations on the sample macro I posted and will be able to modify your code
> to suit.
>
> One interesting thing that is different from what I have been using-with my
> original code it didn't matter which column was selected on the source sheet.
> It appears that your code needs Column A to be selected on the source sheet.
> This is NOT a problem for me.
>
> Once again many thanks for your assistance.
> --
> AJM1949
>
>
> "JMB" wrote:
>
> > 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

Once again I owe you a big thank you. I'm sure all the novices out there
appreciate the time and effort put in by people such as yourself. I have
learnt a lot from what you have shown me.

Regards
AJM1949


"JMB" wrote:

> you're welcome. sorry it took a couple of tries. yes, the macro copies the
> data relative to the column that is selected (another assumption on my part).
> if you ever want to play around w/it, you could make it copy the correct
> rows no matter what column is selected on the source sheet
>
> Current Code: Cells(y.Row, y.Column + 1).Value
>
> The y.column+1 specifies the first column to the right of whatever column is
> selected (which will be column B-and makes this a relative reference). You
> could hardcode the column numbers
>
> Cells(y.Row, 2).Value
>
> which would hardcode the column numbers the way your original macro did.
>
> y.value
>
> would have to be changed to
>
> Cells(y.row, 1).Value
>
> For Column A.
>
>
>
>
> "AJM1949" wrote:
>
> > Many Thanks JMB-Your code has helped me enormously. I have a number of
> > variations on the sample macro I posted and will be able to modify your code
> > to suit.
> >
> > One interesting thing that is different from what I have been using-with my
> > original code it didn't matter which column was selected on the source sheet.
> > It appears that your code needs Column A to be selected on the source sheet.
> > This is NOT a problem for me.
> >
> > Once again many thanks for your assistance.
> > --
> > AJM1949
> >
> >
> > "JMB" wrote:
> >
> > > 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