Dan
Premium Member
Registered: 22nd Apr 02
Location: Gorleston on Sea, Norfolk
User status: Offline
|
Guys, I stole some code from a template, which basically extracts rows of date from sheet 1, and inputs it into various sheets depending on which box has an x in it.
I need it to only start scanning the sheet from A5100, how do I change my code to suit this??
quote:
Private Sub Workbook_Open()
Dim i, LastRow
LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Quoted Work").Range("A2:R500").ClearContents
For i = 2 To LastRow
If Sheets("Sheet1").Cells(i, "P").Value = "x" Then
Sheets("Sheet1").Cells(i, "E").EntireRow.Copy Destination:=Sheets("Quoted Work").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Completed Work").Range("A2:R500").ClearContents
For i = 2 To LastRow
If Sheets("Sheet1").Cells(i, "N").Value = "x" Then
Sheets("Sheet1").Cells(i, "E").EntireRow.Copy Destination:=Sheets("Completed Work").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Work In Progress").Range("A2:R50000").ClearContents
For i = 2 To LastRow
If Sheets("Sheet1").Cells(i, "N").Value = "x" Then
Sheets("Sheet1").Cells(i, "A").EntireRow.Copy Destination:=Sheets("Work In Progress").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
End Sub
Thanks!!
Adult GiftsClick here to vist us
|
evilrob
Premium Member
Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
|
Change all instances of:
For i = 2 to LastRow
To:
For i = 5100 to LastRow
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
As Rob mentions, just change the starting point of the For Loop.
Although, is there any reason why you need to iterate through all the rows three times? Can you not bung everything in a single loop and use IF/ElseIf statements? As this would be a little quicker if you're chugging through loads of data.
|
Dan
Premium Member
Registered: 22nd Apr 02
Location: Gorleston on Sea, Norfolk
User status: Offline
|
I don't know what im doing lol... I just copied 1 line from somebody, and carried it on...
How can I do it more efficiently?
The only thing I like about it, I can change the rows for all to what I want to see, and run a kind of report like that.
Im sure I could probably do something once the sheets are made after opening, with regards to choosing certain dates etc?
Adult GiftsClick here to vist us
|
