On Mar 31, 12:14*pm, Mouimet <Moui...@discussions.microsoft.com>
wrote:
> 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
|
Similar Threads
