View Single Post
  #14   Report Post  
Old August 29th 05, 02:03 PM
Dave Peterson
 
Posts: n/a
Default

It looks like you:

BTR -- turn-in doc
-------- -----------
column A -- D10
N -- B6
Z -- B12
AY -- B17

Do you do something with those values after you copy them to "turn-in doc"? I
did a print preview in this code:

Option Explicit
Sub testme()
Dim prtWks As Worksheet
Dim actWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iRow As Long

Set actWks = ActiveSheet 'BTR is the one with the buttons
Set prtWks = Worksheets("TURN-IN DOC")

Set myRng = Intersect(Selection.EntireRow, actWks.Range("a:a"))

With prtWks
For Each myCell In myRng.Cells
iRow = myCell.Row
.Range("d10").Value = actWks.Cells(iRow, "a").Value
.Range("b6").Value = actWks.Cells(iRow, "n").Value
.Range("b12").Value = actWks.Cells(iRow, "z").Value
.Range("b17").Value = actWks.Cells(iRow, "ay").Value
Application.Calculate
.PrintOut preview:=True
Next myCell
End With
End Sub

If you just copy the values over, then do some manual effort, you'll only want
to get the row with the activecell.

If that's the case, you can change this line:
Set myRng = Intersect(Selection.EntireRow, actWks.Range("a:a"))
to:
Set myRng = ActiveCell

And delete that .printout line.

Optitron wrote:

HERE IS THE CODE I STARTED WITH:

Sub BUTTON1()
'
' BUTTON1 Macro
' Macro recorded 08/29/2005 by harrisonsl
'

'
Sheets("TURN-IN DOC").Select
Range("B6").Select
ActiveCell.FormulaR1C1 = "=BTR!R9C14"
Range("D10").Select
ActiveCell.FormulaR1C1 = "=BTR!R9C1"
Range("B12:E13").Select
ActiveCell.FormulaR1C1 = "=BTR!R9C26"
Range("B17").Select
ActiveCell.FormulaR1C1 = "=BTR!R9C51"
Range("B18").Select
End Sub

HERE'S WHAT I WANT TO HAPPEN. HOPEFULLY THIS ISN'T TOO CONFUSING. I
WROTE THIS SO YOU CAN REPLICATE THIS IN YOUR OWN EXCEL.:

BUTTON1 = MOVE ROW *9* SHEET2 (BTR) CELLS TO SHEET1 (TURN-IN DOC)
CELLS

SHEET2 CELLS
CELL: A*9*|N*9*|Z*9*|AY*9*
VALUE: 05W297|310|Flashlight,Mag,2Cell,withBaton|6230-01-432-6430

SHEET1 CELLS
CELL: D10|B6|B12|B17
CELL NAME: REPORT #|WORKCENTER|NOMENCLATURE|NSN

SHEET2 A*9* TO SHEET1 D10
SHEET2 N*9* TO SHEET1 B6
SHEET2 Z*9* TO SHEET1 B12
SHEET2 AY*9* TO SHEET1 B17

BUTTON2 = MOVE ROW *10* SHEET2 (BTR) CELLS TO SHEET 1 (TURN-IN DOC)
CELLS

SHEET2 CELLS
CELL: A*10*|N*10*|Z*10*|AY*10*
VALUE: 05W297|310|Flashlight,Mag,2Cell,withBaton|6230-01-432-6430

SHEET1 CELLS
CELL: D10|B6|B12|B17
CELL NAME: REPORT #|WORKCENTER|NOMENCLATURE|NSN

SHEET2 A*10* TO SHEET1 D10
SHEET2 N*10* TO SHEET1 B6
SHEET2 Z*10* TO SHEET1 B12
SHEET2 AY*10* TO SHEET1 B17

I need this for every row without recording each macro over and over
again.

Dave Peterson Wrote:
Post your code and what you want to happen.


--
Optitron
------------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...o&userid=26729
View this thread: http://www.excelforum.com/showthread...hreadid=399813


--

Dave Peterson