Simple Excel macro

[madbunny]

If anyone can help me with this problem I'd appreciate it.
I'm trying to make what I'd assume is a simple excel macro but I cannot find any useful FAQs or website with actual useful help on making excel macros.

Basically I want to select a column, let's call it column G, and I want the macro to check every field in that column from say 5 to 100 and if there's a 1 in that field, then get the information in column C in the same row, and put it into the windows clipboard so I can paste it later.

I'm using Selection.Cells(x,1).value in a loop and incrementing x to find the value in the selected column, but how do I then select column C in the same row if it's a 1? Also I'm unsure how to add this information to windows clipboard. I want to be able to ctrl-V once I've run the macro and simple paste the list of information that was retrieved.

If anyone can help me with this or simply point me to a useful online resource I'd appreciate it.

[Justin Philips]

as far as I know you cannot copy multiple cells to the clipboard. A
better solution would be to copy the cells you choose to a new column.


Sub Copy()
Dim x As Single, y As Single

x = 1
y = 1

Do While Range("G" & x).Value <> ""
If Range("G" & x).Value = "1" Then
Range("C" & x).Select
Range("C" & x).Copy
Range("H" & y).PasteSpecial
y = y + 1
End If
x = x + 1
Loop
End Sub

You can replace the Range("H") for wherever you would like the data to
be sent to...I dont know if this helps but it does work!

-Justin

[Tom Ogilvy]

sub addtoclipboard()
Dim rng as Range
for each cell in Range("G5:G100")
if cell.value = 1 then
if rng is nothing then
set rng = cell
else
set rng = union(rng,cell)
end if
end if
Next
if not rng is nothing then
set rng = Intersect(rng.entireRow,Columns(3))
rng.select
rng.copy
else
msgbox "Nothing to copy"
end if
End Sub

Just remember that there are many actions that will clear the clipboard

--
Regards,
Tom Ogilvy


"madbunny" wrote:

>
> If anyone can help me with this problem I'd appreciate it.
> I'm trying to make what I'd assume is a simple excel macro but I cannot
> find any useful FAQs or website with actual useful help on making excel
> macros.
>
> Basically I want to select a column, let's call it column G, and I want
> the macro to check every field in that column from say 5 to 100 and if
> there's a 1 in that field, then get the information in column C in the
> same row, and put it into the windows clipboard so I can paste it
> later.
>
> I'm using Selection.Cells(x,1).value in a loop and incrementing x to
> find the value in the selected column, but how do I then select column
> C in the same row if it's a 1? Also I'm unsure how to add this
> information to windows clipboard. I want to be able to ctrl-V once I've
> run the macro and simple paste the list of information that was
> retrieved.
>
> If anyone can help me with this or simply point me to a useful online
> resource I'd appreciate it.
>
>
> --
> madbunny
> ------------------------------------------------------------------------
> madbunny's Profile: http://www.excelforum.com/member.php...o&userid=32541
> View this thread: http://www.excelforum.com/showthread...hreadid=523253
>
>

[madbunny]

Tom I appreciate the help but Justin was right, although that selects only the individual fields in Column C, when I copy and paste it copys the entire column.

Justin that macro you posted does pretty much what I wanted, I can just copy column H (which I changed to B) But I have another question. Can I change:

Do While Range("G" & x).Value <> ""
If Range("G" & x).Value = "1"

to use the column I currently have selected instead of manually having to change G to a different column? there's about 50 different columns I have to run this macro for.

[Justin Philips]

try this:

Sub Copy()
Dim x, y, c As Single

x = 1
y = 1
c = ActiveCell.Column

Do While Cells(x, c).Value <> ""
If Cells(x, c).Value = "1" Then
Range("C" & x).Select
Range("C" & x).Copy
Range("B" & y).PasteSpecial
y = y + 1
End If
x = x + 1
Loop
End Sub

so will you be running this macro fifty times?
you could have it run through all fifty columns and paste everything
into a new sheet. I dont know what your needs are.
HTH
-Justin

[Tom Ogilvy]

What I provided does what you ask.

If you wanted to replace the 1's in column G with the corresponding value
in column C, then that certainly isn't what you stated.

--
Regards,
Tom Ogilvy


"madbunny" <madbunny.24s7fy_1142544604.325@excelforum-nospam.com> wrote in
message news:madbunny.24s7fy_1142544604.325@excelforum-nospam.com...
>
> Tom I appreciate the help but Justin was right, although that selects
> only the individual fields in Column C, when I copy and paste it copys
> the entire column.
>
> Justin that macro you posted does pretty much what I wanted, I can just
> copy column H (which I changed to B) But I have another question. Can I
> change:
>
> Do While Range("G" & x).Value <> ""
> If Range("G" & x).Value = "1"
>
> to use the column I currently have selected instead of manually having
> to change G to a different column? there's about 50 different columns I
> have to run this macro for.
>
>
> --
> madbunny
> ------------------------------------------------------------------------
> madbunny's Profile:
http://www.excelforum.com/member.php...o&userid=32541
> View this thread: http://www.excelforum.com/showthread...hreadid=523253
>

[Justin Philips]

Yes Tom's does work. Don't forget to add:

Dim cell as Range

You can decide which serves your purposes better.

-Justin

[Tom Ogilvy]

Thanks for the note. To continue the helping:

Dim x, y, c As Single

x is variant
y is variant
c is single

is that what you intended?

Most would do

Dim x as Long, y as Long, c as Long

--
Regards,
Tom Ogilvy

"Justin Philips" <philipsj@gmail.com> wrote in message
news:1142603946.573476.259680@z34g2000cwc.googlegroups.com...
> Yes Tom's does work. Don't forget to add:
>
> Dim cell as Range
>
> You can decide which serves your purposes better.
>
> -Justin
>

[Justin Philips]

oh yeah...thanks!