Copy data using a Macro

[Daksha]

Using a macro how do I copy a range of cells, with formulas, from one range to the next range/column? Then copy the new range of cells to the next column when the macro is run again?

[arlu1201]

Please provide some more details like -

1. Are the ranges in the same workbook?
2. If in the same workbook, are they in the same worksheet or different worksheets?
3. What are the cell addresses of the ranges?

[Rob*]

Something like this?

Please Login or Register  to view this content.

[Daksha]

Please provide some more details like -

1. Are the ranges in the same workbook? - YES
2. If in the same workbook, are they in the same worksheet or different worksheets? ONE WORK SHEET
3. What are the cell addresses of the ranges?

Basically I want to copy the formulas from eg: B1-B10 and paste it into C1-C10, the next time you run this macro it must take the formulas from C1-C10 and paste it into D1-D10. Every time you run the macro it must move on

[arlu1201]

Did you try the code Rob gave you?

[Daksha]

Hi...

Yeah I tried what Rob said, it works but it copies the entire column. I want to copy a range eg B1:B10 to C1:C10...then when I run it the next time it must copy from C1:C10 to D1:D10...

[Kyle123]

Seems to me that:

Please Login or Register  to view this content.

Would suffice

[Daksha]

Thank you for all the help. I am almost where i need to be. Just a few more questions now that the main bit is sorted.

Using the same or another macro I now I need to find out how to copy and paste special the previous range
and is it possible for then use Kyle123's macro on multiple worksheets?

[Kyle123]

Mine will work on the active sheet, so yes, it does that by default.

My method won't work for paste special though. What is it you are needing? Just the values? If so

Please Login or Register  to view this content.

[Daksha]

Thanks Kyle. Let me see if I can explain a bit better what I require..

Ok I have formulas in 17 worksheets in the same place eg B12:B22. When I run the macro I want it to copy that formula to C12:22, then copy and paste special values in B12:B22. The next time I run the macro it must now copy the formulas for C12:C22 to D12:D22, then copy and paste special values in C12:C22 and so on

[Kyle123]

Maybe:

Please Login or Register  to view this content.

Or if you want the ranges in the formulas to increment:

Please Login or Register  to view this content.

[Daksha]

Thank u kyle this works, but ..
where u have "ActiveSheet.Columns.Count" is there a way to change this so that it simulantenously does, "sheet1, sheet2 , sheet 3 etc" whilst using the same range?
Also where you have "Resize(10)" say for example I have 10 rows, which pulls through fine, then there is 2 empty rows and then another 2-3rows that I need brought over so example: "A1:A10 and then A13:A16"

[Daksha]

where u have "ActiveSheet.Columns.Count" is there a way to change this so that it simulantenously does, "sheet1, sheet2 , sheet 3 etc" whilst using the same range?
Also where you have "Resize(10)" say for example I have 10 rows, which pulls through fine, then there is 2 empty rows and then another 2-3rows that I need brought over so example: "A1:A10 and then A13:A16"

[Kyle123]

Potentially:

Please Login or Register  to view this content.

[Daksha]

Potentially:

Please Login or Register  to view this content.

Hi Kyle, I have attempted this and it doesnt work..

the ffg line is incorrect?
With ws.Cells(1, ws.Columns.Count).End(xlToLeft).Range(Cells(1, 1), Cells(ws.Rows.Count, 1).End(xlUp))

[Kyle123]

What error do you get? At a guess you have no data in one of your worksheets

[Daksha]

there is no specific error it just highlights that line in yellow?

[Kyle123]

It must say something?

[Daksha]

Do you think its at all possible for me to get your email address as it wont let me paste a screen dump here? maybe i can mail you the situation I am getting?

[Daksha]

Run-time error '1004':

Application-defined or object -defined error

[Kyle123]

Try This:

Please Login or Register  to view this content.

[Daksha]

Great thank you, it works!! whew!

Is there a way though that this may only run for specific sheets, so I have 17 worksheets, I only require this to run for sheet1 , sheet 2 and sheet 3?

[Kyle123]

Like this:

Please Login or Register  to view this content.