Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do until problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do until problem
On Mar 31, 12:14*pm, Mouimet
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 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 Try this modified code he 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colon at the end of excel file name(ex: problem.xls:1, problem.xls | New Users to Excel | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
problem with a conditional max problem | Excel Discussion (Misc queries) | |||
Problem when multipple users access shared xl-file at the same time, macrocode for solve this problem? | Excel Programming |