vba excel copy rows with condition Solved

Question

Hello,
I've been searching, but I can't find ...
I have 2 sheets in 1 workbook
I want to copy each row from sheet 1 that contains a value in column C to sheet 2
I thought about filters..., but I would prefer a code that allows for all rows to be considered even if I have active filters on the sheet

Configuration: excel97 vba6 under windows

Do you have the solution?

Armojax · Accepted Answer

Re, Ludivine,   A small macro...Sub FiltreLulu() Dim Lig As Long Dim Col As String Dim NbrLig As Long Dim NumLig As Long Sheets("Feuil2").Activate ' destination sheet Col = "C" ' column of the non-empty data to test NumLig = 0 With Sheets("Feuil1") ' source sheet NbrLig = .Cells(65536, Col).End(xlUp).Row For Lig = 1 To NbrLig If .Cells(Lig, Col).Value <> "" Then .Cells(Lig, Col).EntireRow.Copy NumLig = NumLig + 1 Cells(NumLig, 1).Select ActiveSheet.Paste End If Next End With End Sub

JF · Answer

what I will do:
'start
-store the current filter of sheet 1 in a variable
-filter sheet 1 to only include rows with a non-null value in column C.
-copy the entire sheet 1 to sheet 2
-apply the previously saved filter

and it's done (that's the simplest thing)

Armojax · Answer

Hmm... strange. Did you properly adapt the 3 parameters: source sheet name, destination sheet name, column to test?

Armojax · Answer

Sorry, I wasn't here yesterday...
It's a bit hard from a distance... Analyze the context yourself. When it fails, and you have the line highlighted in yellow, don't stop everything, and analyze the context:

1) Display the Local Variables window, and look at the values of the variables. For it to fail on a Select, the NumLig might look weird...

2) Also display the Execution window. You can type commands there. For example, ask what the active sheet is by typing Print ActiveSheet.Name, etc. (you can type a ? instead of Print, it does the same).

In a somewhat brute-force way, I analyze all the lines of the source sheet, including the header lines. You can modify this by changing the starting line in the For... loop. And likewise, I write starting from the first line of the destination sheet. If you have headers, increase the initial value of NumLig accordingly. And it would probably be wise, before all this, to clear the lines from the previous result in the destination sheet. It’s just a template that needs to be adapted...

momo · Answer

I have 6 sheets in a workbook, I would like to copy the rows from sheets 1 to 5 in column I into the rows of sheet 6, column F
S1: 62 rows in column C
S2: 47 rows in column C
S3: 43 rows in column C into rows of sheet 6, column F (sheet 6: 232 rows).
S4: 32 rows in column C
S5: 43 rows in column C

Vba excel copy rows with condition

5 réponses

Windows spotlight is not working.

Samsung j5 unable to answer

Scam of the suvedene highway

Remove 360 antivirus

My pc's screen is flickering.

Fujitsu siemens laptop won't start anymore

Wearline: what do you think?

Eko tank smartwatch

Ibiza sound reviews

Only one of my iphone headphones works. what to do?