Copy & pasting on Excel using macro

Hi, i am new here and i encountered one problem with Macro in Excel.
After i copy one row (range from A to Z) and i want to paste each cell
on odd condition. example; cell A to cell BA, cell B to cell BC, cell C to cell BE. something like that.
i know how to copy the cell within range, but i don't know how to paste on that condition. I need to do it for color pasting.
*copying*
Range("W6:AD6").Select
Selection.Copy

can help me pls.,

ps. can i write lilke ( I = I + 2) declaration.

Hi Boeypc and welcome to the forum. :)

Please be sure to read the posting guidelines (http://www.xtremevbtalk.com/faq.php?faq=evbf_faq#faq_evbf_rules).

I assume that you're copying and pasting within the same row?

So for example,
A6 --> BA6
B6 --> BC6
C6 --> BE6 etc...

I think we should try to keep this as simple as possible.
You can refer to columns numerically using the Cells property - check it out in your Excel VBA helpfiles. And here's an example:


'give the range A6 the value "Hello"
Worksheets(1).Cells(6,1).Value = "Hello"


So now it's just a case of incorporating this in a loop structure. Have a go and let us know if you get stuck.

HTH
Colin

What i want to copy is the color in that cell.
eg. white on A1, black on B1 and yellow on C1, then i copy the whole role
and paste on other role by odd order, like white from A1 to A10, black from
B1 to C10 and yellow from C1 to E10.. and so on...
( paste on A, C, E, G and so on)

Range("A10").Select // in this line, can i set the range to A10,C10,E10 and so on )
ActiveSheet.Paste
Application.CutCopyMode = False

thks :)

Yes, I understand what you're after.

I suggested the approach in #2 because it's simple.

Let me elaborate on what I mean with an example:

Sub Example()
Dim wstColors As Worksheet
Dim i As Integer

Set wstColors = Worksheets(1)

For i = 1 To 26
wstColors.Cells(10, i).Interior.ColorIndex = wstColors.Cells(1, i).Interior.ColorIndex
Next i

End Sub


So this loops through columns 1 to 26 and assigns the colors in row 1 to row 10.

Now what you have to do is try to work out how you would make the column number in the target row step up by 2 instead of 1.

Give it a go and post back with what you have tried if you get stuck.

Colin

Boeypc,

It looks like you might not be familiar with the "cells( )" addressing method (although with a little experimentation you can infer it from Colin_L's example).

The first # is the cell's row and the second number is the cell's column:
cells(1,1) points to the same cell as Range("A1")
cells(1,2) points to the same cell as Range("B1")
cells(2,1) points to the same cell as Range("A2")
and so on.

A couple of other Excel Macro tips:

Click on or highlight a keyword (such as "cells") and press the F1 key to bring up help for the highlighted word. Sometimes you'll need to search a little further once you're in the help window, but this usually gives you a good start.
Examine all the windows and debugging tools built into the Excel VBA interface (where you work with your code; hotkey shortcut is alt + F11). In particular learn to use the locals window in combination with the F8 key to step through your code while watching what each step does.


-geodekl

For i = 1 To 26
wstColors.Cells(10, i).Interior.ColorIndex = wstColors.Cells(1, i).Interior.ColorIndex
Next i

In that code, can i move to "i" by 2 ..
the rest is okie already, and thanks for ur help . :)




Please post Excel questions, in the Excel forum.

Please use the .. tags when you post your code. Edit or reply to this post to see how.

Thank you.

Colin's intention was not to finish the code for you, just to give you a nudge in the right direction. Try and think about it rationally - what do you have to do to go from 1 to 2, from 2 to 4, from 3 to 6, etc. Think of i as just any old number, there's no "magic" here.

ps. can i write lilke ( I = I + 2) declaration.

You're on the right track with that question (one right track, anyway). Also, look at the keyword "Step" in relation to a for..next loop.

Thanks collin and everybody .. :)
i got it ..