Loop and paste data

[pedro53]

I need help in creating a macros that's looks for data on "sheet 1" even if there's blank rows in between and takes that data and copy's it to "sheet 2" as demonstrated in the pictures below.


Attachment 675579Attachment 675580

[karmapala]

Maybe something like this ?

Please Login or Register  to view this content.

FYI, this code will work only if the row Equipment of each columns has a value.
For example, if A5 is blank and in any of the rest of the cell of B5, C5, D5 and E5 has a value, the code can't achieve the expected result.
If F8 is blank and in any of the rest of cell G8, H8, i8, J8 has a value, the code can't achieve the expected result. And so on.

Also, if for example cell A10 to E10 is blank (or only A10 is blank), but any of the rest of the row (cell F10 to O10) has value, the code can't achieve the expected result.

[Paul]

Hi Pedro, I think this macro will work for you. It assumes that there are just three "groups" in each row, e.g. columns A:E, F:J and K:O. If there are more, the code can be adjusted (specifically the line For j = 1 To 11 Step 5).

Please Login or Register  to view this content.

[Paul]

@Karmapala, while copying/pasting would be simpler, the values from the 4th and 5th columns need to be joined with the value from the 3rd column as shown in the example provided by Pedro, resulting in just four columns. I suppose you could add lines of code to your macro to adjust the data once it has been copied over, so it's still doable.

[nankw83]

This is one more approach

Please Login or Register  to view this content.

[karmapala]

@Paul,
You are correct.

I didn't put enough attention on the result where the values from the 4th and 5th columns is joined with the value from the 3rd column.
Thank you for your correction, Paul.

I hope this one is OK :

Please Login or Register  to view this content.

If you don't mind, please review my code, Paul.
Thank you.

[pedro53]

Thanks paul and everone thats helped.

paul now I'm trying to adapt the code to the excel file im going to be using and it did not continue to paste the values.

Attachment 675605

Attachment 675607

it deleted the second row in the 2 picture I forgot to communicate that the first 2 rows should not change. how can I address this.

Please Login or Register  to view this content.

those were the tweaks I made to the code you provided.

[nankw83]

@pedro53, have you tried the code in post #5 ?

[Paul]

Perhaps upload a sample workbook that is laid out like your actual data rather than have me guess (and I also don't want to build a test workbook manually).

As far as the starting row on the second sheet, that is determined by 'lastrow2 = 2'. Change that to 3 to start the new data in row 3.

[karmapala]

@Pedro,

I can't view Attachment 675605 and Attachment 675607, as I got a message :

Invalid Attachment specified. If you followed a valid link, please notify the administrator

[pedro53]

Paul, once again Thanks! it works but if I add more data it won't copy it over, it only copies the first row and that's it.



See the attachment.

[pedro53]

I don't know why but I've uploaded the workbook

[pedro53]

I also have another concern can I, later on, copy this code and paste it under in a different workbook that I use to run the marcos on a different excel workbook.

[Paul]

I downloaded your workbook and ran the macro as-is. It worked just fine for me. One thing to possibly change is the last column reference of 500 to 645, since your columns go out to "XU".

2020-05-01_16-14-44.jpg

You can copy the code to another workbook, but realize you may need to adjust ranges/variables based on the layout of that workbook.

[pedro53]

Thanks Paul it worked,

now i tried to copy it to the workbook I was talking about and it does not run the code.

Please Login or Register  to view this content.

[pedro53]

I think it does not work because the sheets are in a different workbook. Im not sure I'm new to all this.

[Paul]

You *really* need to understand how to explain your issue(s) so that they encompass (as near as possible) what you're trying to accomplish. If you want your code to act on other workbooks/worksheets, you'll need to add code to handle that (e.g. opening the other workbook if necessary, referencing that workbook along with the sheet names in the code, etc.)

As example code (assuming 'SomeOtherWorkbook.xlsx' is already open):

Please Login or Register  to view this content.

If you need additional assistance please start a new thread, as this is far removed from the original question about how to rearrange some simple cells/data. You can reference this thread if necessary (recommended).

https://www.excelforum.com/excel-pro...aste-data.html

[karmapala]

now i tried to copy it to the workbook I was talking about and it does not run the code.

Pedro, I wonder what does that mean.

Do you mean :
A. When you run Sub TCCWriteup(), it does not also run Paul's Sub moveStuff() ?
or
B. After you run Sub TCCWriteup() - then when you run Paul's Sub moveStuff() this sub does not run ?

[pedro53]

A) i would want it to run after it run tccwriteup() I'm trying to insert the code in between like I show below

Please Login or Register  to view this content.

it seem that they used this line to call out the sheet in the other workbook:

pwbProject.Sheets("1.2 - TCC Coordination")