You've helped me many times before and here is another opportunity for you! I continue to be a macro-weenie, and need help with the most simple things!
What I am trying to to is move the cursor down a list of numbers, one at a time. At each number, the macro needs to copy the value, and paste it into a cell on another sheet in the same workbook. Then, print the sheet. Go to the next number, copy it %26 paste to the other sheet, print the sheet... etc...until all the numbers in the list have been copied %26 pasted and the sheet printed.
I have most of this working! Its a DO loop which reads a COUNT of the list, and decrements that till it reaches zero. The simple problem I seem to have is with the COPY and PASTE operation. Here's my code, what am I doing that's so terribly wrong? The value that gets pasted seems not to be the value from the list:
Sub PrintMCS(# ' ' PrintMCS Macro ' This macro will print one MCS form for each WO in the Unique WO list '
Sheets#"LPEC Usage Database").Select Dim Counter Counter = Range("AB6").Value ' Initialize variables.
Do While Counter <> 0 ' Inner loop.
ActiveCell.Offset(1, 0).Range("A1").Select Selection.Copy Sheets("MCS").Select Range("E2:F2").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveSheet.Calculate
' ActiveWindow.SelectedSheets.PrintOut Copies:=1
Counter = Counter - 1 ' Decrement Counter.
Seems simple enough. The range E2:F2 is a merged cell, that's what the macro recorder produced when I recorded the steps. Its where the copied value from the list should go. AB6 contains the count of the list size. AB10 is the top (one cell above) the first value in the list.
Hopefully, you can help me right quick! I'd like an answer by the end of the day Thursday, if you have the time for this. But if not, that's okay too.
Sub PrintMCS() ' ' PrintMCS Macro ' This macro will print one MCS form for each WO in the Unique WO list '
Dim r as Range, cell as Range Worksheets("LPEC Usage Database").Select Dim Counter Counter = Range("AB6").Value ' Initialize variables.
set r = Range("AB10").Resize(counter,1) for each cell in r with Worksheets("MCS") .Range("E2").Value = cell.value .Calculate .PrintOut Copies:=1 End with Next
would be the way I would approach it.
With merged cells, I don't think you can copy a single cell and paste to the multiple cells - I could be wrong, but I know my method works.
Hope that works for you.