PDA

View Full Version : Solved: Selecting multiple cells



paulked
04-18-2012, 11:00 PM
Hi there

I've been trying to select multiple Cells to copy to another sheet but can't get the code to work. I have this that works:


Sub PopulateInvoice()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rd As Range
Dim cnt As Integer
Dim rw As Integer

Set ws1 = Worksheets("Artisan Foods price list")
Set ws2 = Worksheets("Invoice")
cnt = 12

For rw = 20 To 131
Set rd = ws1.Cells(rw, 12)
If rd.Value > 0 And rd.Value < 21 Then
ws1.Cells(rw, 2).Copy
ws2.Cells(cnt, 2).PasteSpecial Paste:=xlPasteValues
cnt = cnt + 1
End If
Next

End Sub

But I would like to

ws1.Cells((rw, 2), (rw, 3), (rw, 6), (rw, 12), (rw, 13)).copy


How do I do that?

Many thanks

BrianMH
04-19-2012, 12:05 AM
Sub PopulateInvoice()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rd As Range
Dim cnt As Integer
Dim rw As Integer

Set ws1 = Worksheets("Artisan Foods price list")
Set ws2 = Worksheets("Invoice")
cnt = 12

For rw = 20 To 131
Set rd = ws1.Cells(rw, 12)
If rd.Value > 0 And rd.Value < 21 Then
Union(ws1.Cells(rw, 2), ws1.Cells(rw, 3), ws1.Cells(rw, 6), ws1.Cells(rw, 12), ws1.Cells(rw, 13)).Copy
ws2.Cells(cnt, 2).PasteSpecial Paste:=xlPasteValues
cnt = cnt + 1
End If
Next

End Sub




This works. There are more elegant solutions but it's early in the morning and this will do what you want.

snb
04-19-2012, 03:38 AM
If your table is in A1 etc.,
If you want all rows that do not contain "aa" in column B,
If you only want the columns 1,3,5 and 7,
if you want to get all selected values to be put into cell A20 and further,
Then use:

Sub snb()
sn = Cells(1).CurrentRegion
c02 = Cells(1).CurrentRegion.Columns(2).Address

sq = Application.Transpose(Filter(Evaluate("transpose(if(" & c02 & "<>""aa"",row(" & c02 & "),""~""))"), "~", False))
sp = Application.Index(sn, sq, Array(1, 3, 5, 7))

Cells(20, 1).Resize(UBound(sq), UBound(sp, 2)) = sp
End Sub

paulked
04-19-2012, 04:09 AM
Thank you both very much.

Working a dream Brian, who needs elegance 1st thing in the morning anyway? :devil2:

:beerchug: Paul Ked

paulked
04-19-2012, 04:17 AM
I can't mark this as solved, option doesn't appear in thread tools?

msuresh
04-19-2012, 11:37 PM
You may check in IE browser to mark it solved

paulked
04-20-2012, 01:56 AM
You may check in IE browser to mark it solved

I can't mark this as solved, option doesn't appear in thread tools?