AUTOFILL RANGE ? 1

[andrew299]

Hello everyone
What I want to do is copy a list of values from one sheet to another ( range("b2:b52901")) but I want to add a value to it depending on the value from a for..next function


ex
x=0 x=1 x=2
sheet1 sheet2 sheet3
1 2 3
2 3 4
4 5 6
1 2 3

What I have tried so far is

ActiveCell.Formula = ThisWorkbook.Worksheets("ORIGINAL DATA").Cells(2, 2).Formula + X

and then an autofill. but this only copies the value from cell b2 through the range. I can see that my code is stuck looking at b2 though but dont know how to make it more dynamic. I could try

for y = 1 to no.of rows
activecell.offset(1,0).select
ActiveCell.Formula = ThisWorkbook.Worksheets("ORIGINAL DATA").Cells(2+y, 2).Formula + X
next y

but I can't help thinking that this will take a long time considering the number of rows and the number of sheets the macro will be creating
Any suggestions?

Thanks
Andrew299



It may have hit every branch on its way out of the ugly tree, but hey! It works. (but don't quote me on that)

 

[TonyJollans]

Hi Andrew299,

I'm sure we can sort you out, but I am not clear as to what x is. You mention a For..Next loop but your code seems to show x as a constant. Can you post a bit more info please

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[andrew299]

Tony
X is my constant which comes from my for..next loop.
for X = 1 to lots
sheets.add

ActiveCell.Formula = ThisWorkbook.Worksheets("ORIGINAL DATA").Cells(2, 2).Formula + X

next x

Andrew

It may have hit every branch on its way out of the ugly tree, but hey! It works. (but don't quote me on that)

 

[TonyJollans]

Hi Andrew,

Are you trying to get formulae or values into the cells on your new sheets?

You could do something like this quite easily:

[blue]For X = 1 to lots
    :
    :
    Sheets.Add
    ActiveSheet.Range("B2:B52901").FormulaArray = "='ORIGINAL DATA'!B2:B52901+" & x
    :
    :
Next[/blue]

If you then wanted values only you could add ..

[blue]    ActiveSheet.Range("B2:B25").Copy
    ActiveSheet.Range("B2:B25").PasteSpecial xlValues
    Application.CutCopyMode = False[/blue]

Please say if I've completely missed the point.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[andrew299]

Tony
That first bit of code is exactly what I want.
Thank you very much for your help
Andrew

It may have hit every branch on its way out of the ugly tree, but hey! It works. (but don't quote me on that)