Copy a cell, paste it, using a macro

Question

  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.

   Range("AB10").Select

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.
   

Loop

End Sub

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.

Answer

Bill,

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

End Sub

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.