Do until problem

[Mouimet]

HI,
When I type a associate number the in cell C7 another sheet retrieve all
info and I print this info. I have many associate number and I do not want to
type one by one and print one by one.
I need a macro to do all numbers at once.
The data are in Column B starting at B11
The paste is always at C7.

Example: Copy B11 to C7, do the print and return back to B12. Do copy and
paste to C7, print and go back to b13, etc.
Here the VB. The only problem is to go back one row under the last copy.
Print is ok.
=========================================
Sub doUntil9999999()
'Message wait please
Please_Wait.Show False

' Start selecting associate number
' Start at B11 copy, go to C7 paste value, Print then go to the other
associate
' number if it's not 9999999, Starting at b10 to B400.
' Need to go down 1 row at a time column B and copy each time in Cell C7
this new associate number.
' My problem is after printing the first associate (B11) I can not return
from C7 to the other row

Range("B11").Select
Do Until ActiveCell.Value = 9999999

ActiveCell.Select
Selection.Copy
Range("C7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

' trouble here How to go back to the last cell
' The function below go under C7
' not after the last associate number B11,b12,b13, etc.

ActiveCell.Offset(1, 0).Select

' Printing here is ok
Application.ScreenUpdating = False
Sheets("Calendrier").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Liste").Select
Application.ScreenUpdating = True
Loop
'remove message
Please_Wait.Hide
Menu_Print.Hide
End Sub

 

[crferguson]

HI,
When I type a associate number the in cell C7 another sheet retrieve all
info and I print this info. I have many associate number and I do not wantto
type one by one and print one by one.
I need a macro to do all numbers at once.
The data are in Column B starting at B11
The paste is always at C7.

Example: Copy B11 to C7, do the print and return back to B12. Do copy and
paste to C7, print and go back to b13, etc.
Here the VB. The only problem is to go back one row under the last copy.
Print is ok.
=========================================
Sub doUntil9999999()
'Message wait please
Please_Wait.Show False

' Start selecting associate number
' Start at B11 copy, go to C7 paste value, Print then go to the other
associate
' number if it's not 9999999, Starting at b10 to B400.
' Need to go down 1 row at a time column B and copy each time in Cell C7
this new associate number.
' My problem is after printing the first associate (B11) I can not return
from C7 to the other row

Range("B11").Select
Do Until ActiveCell.Value = 9999999

ActiveCell.Select
Selection.Copy
Range("C7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 ' trouble here How to go back to the last cell
 ' The function below go under C7
 ' not after the last associate number B11,b12,b13, etc.

ActiveCell.Offset(1, 0).Select

' Printing here is ok
Application.ScreenUpdating = False
    Sheets("Calendrier").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Sheets("Liste").Select
    Application.ScreenUpdating = True
Loop
'remove message
Please_Wait.Hide
Menu_Print.Hide
End Sub

Try this modified code here:

Sub doUntil9999999()
dim sCell as String 'added this line

'Message wait please
Please_Wait.Show False


' Start selecting associate number
' Start at B11 copy, go to C7 paste value, Print then go to the other
associate
' number if it's not 9999999, Starting at b10 to B400.
' Need to go down 1 row at a time column B and copy each time in Cell
C7
this new associate number.
' My problem is after printing the first associate (B11) I can not
return
from C7 to the other row


Range("B11").Select
sCell = ActiveCell.Address 'added this line

Do Until ActiveCell.Value = 9999999

Range(sCell).Select 'changed this line
Selection.Copy
Range("C7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range(sCell).Select 'added this line


' trouble here How to go back to the last cell
' The function below go under C7
' not after the last associate number B11,b12,b13, etc.


ActiveCell.Offset(1, 0).Select
sCell = ActiveCell.Address 'added this line

' Printing here is ok
Application.ScreenUpdating = False
Sheets("Calendrier").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Liste").Select
Application.ScreenUpdating = True
Loop
'remove message
Please_Wait.Hide
Menu_Print.Hide
End Sub