For loop while copy and pasting specific columns

Question

I need a loop that will match and select different columns (not in sequential order) and paste them to another sheet all whilst keeping the condition in check. It would also be ideal if when the values get pasted that the formatting for the cell is not carried over, just the value.

Below is the code I am currently using:

Sub Test() Application.ScreenUpdating = False Sheets("DATA").Select lr = Range("B" & Rows.Count).End(xlUp).Row Range("P3").Select For i = 3 To lr If Cells(i, 2) <> "" Then Range(Cells(i, 7), Cells(i, 16), Cells(i, 26)).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) Next Application.ScreenUpdating = True End Sub

The problem is declaring the columns I want the loop to paste. I need the loop to run through the 16th column, check empty values, and then paste the index/matched value in the rows of columns 7,16,and 26 (so not in sequential order).. Any help would be appreciated.

Do you mean to loop in a range defined as: first column - 16th column up to the last used row? And for each cell not being empty to paste those specific cells from columns 7, 16, 26 in another sheet? If not, what " I need the loop to run through the 16th column" means? The paste operation to be done in columns A, B, C, or in corespondent columns from where they have been collected (7, 16, 26)? — FaneDuru
– FaneDuru, Commented Jan 9, 2020 at 16:20
@FaneDuru I understand your confusion. The loop needs to run through the P column (#16) and check if there are any blank cells. For every cell that isn't blank, I need the code to then match the non blank cell in the P column with its value in the G,P, and Z columns (based on the row) and then paste those values in a different sheet. — Jeremie Tomkins
– Jeremie Tomkins, Commented Jan 9, 2020 at 16:31
When you say "match the non blank cell in the P column with its value in the G,P, and Z columns", what do you mean? Will they be concatenated, added (if numbers) and pasted in one column, or copy their values in other three columns of the second sheet? If separate columns, to be A, B, C or G, P and Z? I am asking because I intend to make some code able to solve your problem, but I do not like Sisyphus work... — FaneDuru
– FaneDuru, Commented Jan 9, 2020 at 16:51
@FaneDuru Right, sorry about the confusion. The latter of what you said is correct: "copy their values in other three columns of the second sheet" So just to be clear if cell P2 reads: 85 and on the same row cell G2: reads 123 and Z2 reads: House.. Then the code needs to copy those three values and paste them in columns A, B, C of the other sheet. But if a value in column P is blank, the code needs to skip the copy process for that row. Not sure if that helps? — Jeremie Tomkins
– Jeremie Tomkins, Commented Jan 9, 2020 at 18:23
Sorry, but still not very clear... So, the code must copy those three columns value from the examined row ONLY IF ALL THE ROW does not cpntain any empty cell? — FaneDuru
– FaneDuru, Commented Jan 9, 2020 at 18:29

FaneDuru · Accepted Answer · 2020-01-10 14:44:15Z

The next code has to do what I understood you need. Please check it and confirm this aspect. It is very fast, working only in memory...

Sub PastingNextPage() Dim sh As Worksheet, sh1 As Worksheet, arrIn As Variant, arrOut() As Variant Dim lastRowIn As Long, lastRowOut As Long, nonEmpt As Long, rngP As Range, nrEl As Long Dim i As Long, j As Long, P As Long Set sh = Sheets("DATA"): lastRowIn = sh.Range("P" & sh.Rows.count).End(xlUp).Row Set sh1 = Sheets("Sheet2"): lastRowOut = sh1.Range("A" & sh1.Rows.count).End(xlUp).Row + 1 arrIn = sh.Range("G2:Z" & lastRowIn).Value nrEl = lastRowIn - Application.WorksheetFunction.CountIf(sh.Range("P2:P" & lastRowIn), "") - 2 P = 10 'column P:P number in the range starting with G:G column ReDim arrOut(nrEl, 3) 'redim the array to keep the collected values For i = 1 To lastRowIn - 1 If arrIn(i, P) <> "" Then arrOut(j, 0) = arrIn(i, 1): arrOut(j, 1) = arrIn(i, P): arrOut(j, 2) = arrIn(i, 20) j = j + 1 End If Next i sh1.Range(sh1.Cells(lastRowOut, "A"), sh1.Cells(lastRowOut + nrEl, "C")).Value = arrOut End Sub

It does not select anything, you can run it activating any of the two involved sheets. I would recommend to be in "Sheet2" and see the result. If you want to repeat the test, its result will be added after the previous testing resulted rows...

If something unclear or not doing what you need, do not hesitate to ask for clarifications.

Collectives™ on Stack Overflow

For loop while copy and pasting specific columns

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related