Increment column in loop
 

Hello, I'm trying to create a loop which selects data in multipl
worksheets and pastes it into another sheet. It would be doing tw
columns at a time. In other word, the first iteration would be pastin
to columns 1-2, the 2nd 3-4, 3rd 5-6, etc. However, Excel uses letter
for their column headers. How can can I tell it to increment the colum
on every iteration of the loop?

Below is a (probably bad) illustration of what I want to do:
Worksheet
Code
-------------------
AA
AA
A
-------------------
+Worksheet
Code
-------------------
BB
BB
B
-------------------
+Worksheet
Code
-------------------
CC
CC
C
-------------------
=Different Shee
Code
-------------------
AABBCC
AABBCC
AABBC
-------------------


--
Message posted from http://www.ExcelForum.com

Increment column in loop
 

You could try writing a function that changes column numbers t
letters.

So, if the function recieves 27 as a parameter it would return th
string "AA".

It would look something like this.

1 would generate "A"
3 .... "C"
26 ...."X"
27 ....."AA"

And so on.

The iteration would be easier I think.

Don't know if that helps......

--
Message posted from http://www.ExcelForum.com

Increment column in loop
 

That would work if I knew how to convert them to letters like that

--
Message posted from http://www.ExcelForum.com

Increment column in loop
 

the cells object can use column numbers

for i = 1 to 10 step 2
msgbox cells(1,i).Resize(2).Address & " - " & _
Columns(i).Resize(,2).Address
Next

as an example.

So let us say you want to consolidate data to a sheet named Data from 4
other sheets

Dim varr as Variant, j as long, i as long
Dim sh as Worksheet
varr = Array("Sheet1","Sheet2","Sheet3","Sheet4")

numSheets = ubound(varr) - lbound(varr) + 1
j = -1
for i = lbound(varr) to ubound(varr)
j = j + 2
set sh = Worksheets(varr(i))
sh.Columns(1).Resize(,2).copy _
destination:=worksheets("Data").Cells(1,j)
Next


--
Regards,
Tom Ogilvy


"RyanVM " wrote in message
...
That would work if I knew how to convert them to letters like that.


---
Message posted from http://www.ExcelForum.com/

Increment column in loop
 

Got it, thanks :)


---
Message posted from http://www.ExcelForum.com/