CODEWITHSUNDEEP
vbaexcel
babou
babou

Reputation: 237

copy a column with a variable start

This is what I have:

enter image description here

And my probleme is simple: I want to copy all rows Under "Maintenance" (C8) in worksheet 2, BUT I want it to work even if we delete some rows above "Maintenance" (C8).

for example:

enter image description here

because it changes the start of the copy. (from 8 to end before, and 5 to end after)

Here is my code :

n = 8 
j = 2 
Sheets("1").Select 
Do While Cells(n, 1) <> "x" 
   If Cells(n, 1) <> "x" Then 
    Sheets("2").Cells(j, 2) = Sheets("1").Cells(n, 2) 
    Sheets("2").Cells(j, 3) = Sheets("1").Cells(n, 3) 
    j = j + 1 
   End If 
 n = n + 1 
Loop

Upvotes: 1

Views: 28

Answers (1)

RGA
RGA

Reputation: 2607

Use the Range.Find() method to find where "Maintenance" starts, then pull the data below it until whatever condition defines the end of that data (empty cells or a new category name)

For OP's code:

n = Sheets("1").UsedRange.Find("Maintenance",LookAt:=xlWhole).Row

Upvotes: 1

Related Questions