Looping Macro

[kopower]

I am hoping someone can help me I have been trying to do a loop but cannot figure it out. I have two workbooks the first workbook is called Summary.xlsx , in this workbook I have rows of information which I would like to pull into another workbook. What I would like to do is copy the information from the last row in column A paste it into workbook 2 the then go to the next row in column A and paste it in workbook 2, then next row in column A paste into workbook 2 etc until there is no more data in column A. My looping code that I have is as follows;

Set myData = Workbooks("Summary.xlsx")

Set sh1 = ThisWorkbook.Sheets("Option")
Set sh2 = myData.Sheets("Summary")


finalrow = sh2.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To finalrow

"I have code that goes in here and the code works and copies it over to workbook in sh1 no problem"

next i

The code will copy the A1 but will not go to A2 it keeps repeating A1.

Hoping someone can help identify what I am doing wrong.

I am hoping this makes sense the code I have in the middle is pretty big and works but it will not grab the A2, A3, A4 etc

Thanks in advance for any suggestions and help

KO

 

[Twollaston]

what I am trying to do is do a loop that will take A1 in Sh2 and paste into Sh1 then loop to take A2 in Sh2 and drop it is the same spot so for the example provided it in theory is over writing the information that has come over from A1. That is OK as the code is much larger than what I have provided so there are more things going on so the information in A1 will be performing another task before it A2 comes over. But my problem is that it only bring over A1 it does not go to A2 and so on.

I do not understand why it does not go to A2, it keeps copy the information from A1 in tis example 3 times where it should be doing A1, then A2 then A3.

Hopefully I am making sense

KO

which cells do you want to copy to in sheet 1 and what is the order. So first you want
A1 -> D4,
then B1 -> D5,
then D1 -> D7,
then E1 -> D8
then F1 -> D9

then it just copies over that same range 3 more times
it's because in your copy your only changing the column, and then when you're pasting you're only changing the C value, which is linked to column D so since the column is fixed to that, and the i value isn't fixed to a row number(i'm gessing you wanted to move it to the right into the E column?)

like this?

	D	E	F
4	1	1	1
5	2	2	2
6
7	4	4	4
8	5	5	5
9	6	6	6

<colgroup><col span="4"></colgroup><tbody>
</tbody>

For this one you can use

    For i = 1 To MyRng.Rows.Count       For C = 1 To 2 'change the numbers to suit
       sh2.Cells(i, C).copy
       sh1.Range("D" & C + 3).Offset(0, i - 1).PasteSpecial Paste:=xlPasteValues
    Next C
    For C = 4 To 6 'change the numbers to suit
       sh2.Cells(C).copy
       sh1.Range("D" & C + 3).Offset(0, i - 1).PasteSpecial Paste:=xlPasteValues
    Next C
    
    Next i

 

[kopower]

Hi Mark,

I think the code in the middle is causing confusion the For C is not the issue. This part of the code works it is taking the information from Sh2 and pasting it into the proper cells in Sh1. No issues here, my problem is after it takes the A1 from Sh2 and pastes it over to Sh1, I want it to loop to take A2 from S2 and paste that into Sh1, once that is completed paste A3. In theory it seems pretty easy but it will not loop to A2.

 

[Twollaston]

Hi Mark,

I think the code in the middle is causing confusion the For C is not the issue. This part of the code works it is taking the information from Sh2 and pasting it into the proper cells in Sh1. No issues here, my problem is after it takes the A1 from Sh2 and pastes it over to Sh1, I want it to loop to take A2 from S2 and paste that into Sh1, once that is completed paste A3. In theory it seems pretty easy but it will not loop to A2.

You keep saying you want A2 to come over from sheet 2 to sheet 1 but you never say what cell you want to put it in. So you are okay with A1 -> D4, and before the next row updates it has to loop through C, meaning it in now moving columns B1 (Skips C1 because for c there is no c=3 it's only 1-2 and 4-6 per your limits)then D1 E1 F1. If you want some help, You gotta start saying what cells you want it to paste to. So are you saying you want A1 -> D4, and then you want it to move straight to A2 -> and then paste that where? well it's not going to do that, because you have a column loop set after your row loop, so now it has to go through all the columns you specified for row 1 to pull your data from sheet 2 e.g. c 1(A),2(B),4(D),5(E),6(F)

Are you saying you want the column loop first so it loops all the rows from column 1 sheet 2 to cell D in sheet 1? because then you would have to switch your loops

 

[kopower]

Hi Mark,

The For C loop is pasting everything from Sh2 into Sh1 into the correct places in Sh1. Now I want to take A2 from Sh2 and put it in the same place in Sh1 as A1 so it may look like it is overwriting the values from A1. This may seem pretty strange overwriting the information but it is a very large code which retrieves all the information then performs a save as it into a newly created workbook. It then closes the new workbook and clears all of the data in Sh1. Now that Sh1 is clear (no information in D4 etc,) I want to get the data from A2 in Sh2 and bring it in Sh1 in the same spots and then creates new workbook clears Sh1 and loops to A3 in Sh2 etc. o Yes it would be a double loop, first the For C loop then completes the rest of the code and now the new loop that will do it al again based upon A2, A3 etc from Sh2

 

[MARK858]

The code I posted does what you requested, it copies from sh2 A2 to the last cell in column A with data in sh2 to sh1 cell A1. It doesn't use the c variable. The A1 probably needs changing as you won't tell us exactly where it needs to copy to.

 

[kopower]

Hi Mark, I am confused that you keep saying that I am not telling you where it needs to be copied to. In the code that I have provide A1 in Sh2 is going to D4 in Sh1, B1 in sh2 is going to D5 in Sh1, D1 in Sh2 is going to D7 in sh1, E1 in Sh2 is going to D8 in Sh1, F1 in Sh2 is going to D9 in Sh1. What I am trying to do is once all of the information is pasted over starting in A1 in Sh2 into Sh1, I want it to loop to A2 in Sh2, therefore A2 in Sh2 is going to D4 in Sh1, B2 in sh2 is going to D5 in Sh1, D2 in Sh2 is going to D7 in sh1, E2 in Sh2 is going to D8 in Sh1, F2 in Sh2 is going to D9 in Sh1.

 

[Twollaston]

Hi Mark, I am confused that you keep saying that I am not telling you where it needs to be copied to. In the code that I have provide A1 in Sh2 is going to D4 in Sh1, B1 in sh2 is going to D5 in Sh1, D1 in Sh2 is going to D7 in sh1, E1 in Sh2 is going to D8 in Sh1, F1 in Sh2 is going to D9 in Sh1. What I am trying to do is once all of the information is pasted over starting in A1 in Sh2 into Sh1, I want it to loop to A2 in Sh2, therefore A2 in Sh2 is going to D4 in Sh1, B2 in sh2 is going to D5 in Sh1, D2 in Sh2 is going to D7 in sh1, E2 in Sh2 is going to D8 in Sh1, F2 in Sh2 is going to D9 in Sh1.

Because everytime you said it you weren’t specific about which point is the error, so we don’t know which part of the code to fix. So we were thinking the error is between a1 to B1 but it was really after f1 is pasted

Change
sh2.Cells(C).copy
To
sh2.Cells(i, C).copy

 

[MARK858]

Hi Mark, I am confused that you keep saying that I am not telling you where it needs to be copied to.

That's because you hadn't told us what you wanted the code to do, all you had done previously was refer us back to code that wasn't working the way you wanted it to.

We have something to go on now you have stated the destinations. Try the suggestion Twollaston has posted and reply back with any issues (please don't refer us to the code for any issues, explain what the issues are) and I will look at it then (now we can see the destinations it isn't that difficult to write fresh code for it as it follows a pattern).

 

[kopower]

OMG it worked I ant to thank both Twollaston and Mark for all you help in solving this problem. You guys are absolutely amazing!!!! Now I fully understand why you needed the information for For C part as I would never have figured that out. I would also like to apologise for not being so clear upfront (my bad). You guys have saved me weeks of work as there are over 3000 files that needs to be looped through. Thank you again!!!!!! This is by far the best message board!!!!

 

[Twollaston]

OMG it worked I ant to thank both Twollaston and Mark for all you help in solving this problem. You guys are absolutely amazing!!!! Now I fully understand why you needed the information for For C part as I would never have figured that out. I would also like to apologise for not being so clear upfront (my bad). You guys have saved me weeks of work as there are over 3000 files that needs to be looped through. Thank you again!!!!!! This is by far the best message board!!!!

No problem buddy, happy to help you