Using array data for copying ranges to new sheets!

Hi,

I'm writing a small VBA code and now I'm stucked.

I have to say that I only have very little knowldedge about VBA.

However, I have an array in the following format:

H I J K L M
2 15 24 55 39 10
58 75 83 122 102
125 142 148 186 167
189 204 212 246 229
249 266 274 312 293
315 326 332 359 345
362 379 387 425 406
428 443 452 486 469
489 498 506 528 517

Cell M1 indicates that I have to export datasets to 10 new sheets (=number of rows, can vary between 10 and 50)

First step I want to do:

Copy range A(I1+1) : A(J1+I1-2) to new sheet(1) to location A1
Copy range A(J1+3) : A(K1-J1+5) to sheet(1) to location E1
Copy range A(K1-L1+4) : A(L1-i1-1)

Second step I want to do:

Repeat step 1 for all rows 2 to M1 and copy each set of data to a new sheet as well.

How should be my approach to master this task?

Any help is appreciated.

Cheers,
hegisin

Try this macro for starters
I hope I interperated the part of the coping rows correctly.
You did not say where Copy range A(K1-L1+4) : A(L1-i1-1) was to be pasted to.

Let me know if the macro needs fixing in regards to the row calculations.

Once this 1st part of the macro is correct then it should be an easy step to add the required number of loops - just need to know where the get pasted to.

Please Login or Register  to view this content.

Hi mudraker,

Thank you so much for your effort.

The last part has to be pasted to new sheet(k1).

However, I already modified your macro according to my requirements and it executes as it should. Fantastic!

I also studied the method of your macro and I simply didn't know how to use the range.value property correctly.

Now, I still have to loop your macro. m1 number of times.

After that I have to apply one macro (which I have already written) to all new worksheets.

Your advice in this matter is again highly appreciated.

Cheers,
hegisin

Hi mudraker!

Thanks again. I've already written the loops to copy all rows and to run the extra macros within the new sheets.

Your help has probably saved me a day of work and a potential heavy headache.

So now I can look forward to get my headache from other Friday nights activites!

Cheers,
hegisin

Glad to hear you got the last part sorted out.

Post a copy of your macro & I will have a look at to see if I can offer any improvements that may help you in the future.

Not sure I like the Friday night headache bit as come Monday you may forgoten what you are doing and be back for more advice (just joking) enjoy you night out

Hi mudraker,

Here you will find my macro which includes the second part.

To run existing macros within each worksheet, I believe I can use the same loop as for creating the new sheets.

Cheers,
hegisin

hegisin

A couple of things

In the code you posted you have a variable rN that you set as counter + 1. I do not see anywhere you use this within the macro.

Recomendation No. 1
When using for loops use Next & the for variable name. This can help make debugging easier. In your macro this would be Next counter.

Recomendation No. 2
When declaring variables use a code that tells you what type it is.
In my code I use i??? for Integers, ws or ws??? for worksheets, s??? for Strings. Again it can also help make debugging easier


Recomendation No. 3
When declaring variables always use at least 1 upper case letter. When you enter it later in the code it will change to the same upper & lower case format as it is in the Dim statement. This helps to pickup misspelt variables. Again it can also help make debugging easier

Re running existing macros on other sheets - a loop can certainly do that.

Hi mudraker,

Thanks for your feedback.

I just started to have a look at VBA a couple of days ago since I could not find solutions in Excel. So I'm glad for any advice.

Just one remark regarding the variable rN. I included it as the counter has to run from 0 until the value of "m1" which is a variable by itself. So I can't use the for next function here. Please correct me if I'm wrong.

Cheers,
hegisin

hegisin

Please forgive me I had a moment of blidness & insanity whith my previous reply.

Scrap what I said about not using rN with in your macro

A for loop can start at any number it does not have to start at 0
By changing from 0 to 1 you remove the need to subtract 1 form m1 value and you remove the need for a seperate row counter

change

For counter = 0 To (wsAct.range("m1").Value) - 1
rN = counter + 1
to

Please Login or Register  to view this content.

You can then replace every other rN with the variable counter