Is there a way to select the cell in the adjacent column 2

[Forrest77]

Maybe I am going about this wrong. What I have so far is the following that was provided by this site on an earlier question.
Dim r As Range
'right oneFor Each r In Range([b12], [b12].End(xlDown))
For Each r In Range([b12], [b12].End(xlDown))
Range(r, r.Offset(0, 4)).Copy [s30]
Range(r, r.Offset(0, -1)).Copy [r30]
'now do the other stuff.
The above is used to run thru the column b starting at row 12 and copy the cell and the 4 cells to the right of it and one cell to the left of it and copy them to a location s30 and r30. After they are in r30, I have a userform that refers to those cells and then I print the userform. Then I clear the r30& s30 and do it all over again until I run out of data in the column.
What is going to happen is that future rows are going to be added to Range b12 on down and I need to not redo all the copying of the ones that I just did and just do the newly added ones without clearing the cells of the old ones. I figure if I can mark the adjacant cells, I can setup a print command button that will print by referencing the adjacnt cell in the next column rather than the orginial one.
I am sorry this is so drawn out. I appreciate this site immensely and all your help.
Todd

 

[SkipVought]

Hi,

Make your read range only get visible cells

Mark the rows that are processed.

Hide marked rows...

For Each r In Range([b12], [b12].End(xlDown))[b].specialcells(xlcelltypevisible)
  Range(r, r.Offset(0, 4)).Copy [s30]
  Range(r, r.Offset(0, -1)).Copy [r30]
  Range(r, r.Offset(0, 5)).value = "DONE"

Next

filter on column E <> "DONE"

Skip,
[sub]
[red][/red]
[/sub]

 

[Forrest77]

Thanks Skip,
I'll give it a try and get back to you. I really appreciate your time.
Todd

 

[Forrest77]

Skip,
I gave it a try and first it would not let me use this part:
.SpecialCells (xlCellTypeVisible)
Even though I don't want to print those previous rows, I do want to be able to see the old rows with the new rows after them.
The placing of the word "Done" is what I think will work but the code places "Done" in all 5 cells and overwrites the 1st 4 cells of Data. Is there a way just to have the "Done" In the 5 cell over instead of all 5 cells in the row?
Thanks again. I will work with it and see if I can get it.
Todd

 

[SkipVought]

sorry

r.Offset(0, 5).value = "DONE"

You can hide the rows only when you Print.

Skip,
[sub]
[red][/red]
[/sub]

 

[Forrest77]

Skip,
You are fantastic. Code works perfectly. You have made a lot of people happy with that code. I am always amazed and astonded by the information on this site.
Thank You Ver Much,
Todd

 

[Forrest77]

Skip,
I hate to bug you but I am stuck again. I now have the rows printing and adding the Done to the cell that is offset by 5. Now I am trying to print any new rows that do not have that.
This is pretty much what you have given me:
Dim r As Range
For Each r In Range([b12], [b12].End(xlDown))

Range(r, r.Offset(0, 5)).Copy [s30]
Range(r, r.Offset(0, -1)).Copy [r30]
r.Offset(0, 5).Value = "DONE"
'now do the other stuff until use Next
So I tried to an an if/then statement line like:
If r.Offset(0, 5).Value = "" Then GoTo line15
Else: GoTo line20
I took away the r.Offset(0, 5).Value = "DONE" and put it
Where line 20 has the necessary "Next" and line 15 just goes down and finishes the rest of the "do other stuff" that works just fine before I put in the if/then statement.
When I put that if/then statement inbetween the For and Next, I get the yellow error highlighting the next and it wants a "For" reference which is at the very beginning and worked before the if/then. I am guessing at the "If r.Offset(0, 5).Value = "" Then GoTo line15" statement and it does not seem to error out there.
Have not a clue where to go next.
Todd

 

[PHV]

your if/then should be either like this:
If r.Offset(0, 5).Value = "" Then
GoTo line15
Else
GoTo line20
End If

or like this:
If r.Offset(0, 5).Value = "" Then GoTo line15 Else GoTo line20

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Forrest77]

Thanks Skip,
You did it again. It works just perfectly. Thanks for all your help. This should get me right where I need to be.
Again Thank You for your time and help. It is most appreciated.
Todd

 

[Forrest77]

Thanks PHV,
You finished this up just perfectly. I at first thought it was Skip that had again answered my post. Your If/Then works just perfect. So Thanks again PH, Your help and expertise is a gift and I thank you for it.
Todd