Excel macro question

[Kirby]

I've inherited a excel 2003 workbook thats an inventory listing of devices.
It has 3 worksheets, the 1st sheet lists devices on each row with column
headings like device name; serial #; location, etc. The 2cd is a link to the
3rd which is a form that gets printed out. So you copy your device info from
sheet 1 to sheet 2 and it fills in the form. My ? is there a way to select
say row 2 then run a macro that says if a row is selected copy A2 to O2 to
sheet 2 A1 to O1? And is it also possible to then have a macro that prints
out the form on sheet 3? This would eliminate a lot of manual steps.

 

[Bob Phillips]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Me.Cells(Target.Row, "A").Value <> "" Then

Me.Cells(Target.Row, "A").Resize(, 15).Copy _
Worksheets("Sheet2").Range("A1")
Worksheets("Sheet3").PrintOut
End If
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Kirby]

Thanks Bob I'll give it a try
--
Thank you in advance
Kirby

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Me.Cells(Target.Row, "A").Value <> "" Then

Me.Cells(Target.Row, "A").Resize(, 15).Copy _
Worksheets("Sheet2").Range("A1")
Worksheets("Sheet3").PrintOut
End If
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Kirby]

Bob it works great, slight problem i can't select a cell and update it
without it printing. is there a way i could manually run the code after i've
updated and selected the row?
--
Thank you in advance
Kirby

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Me.Cells(Target.Row, "A").Value <> "" Then

Me.Cells(Target.Row, "A").Resize(, 15).Copy _
Worksheets("Sheet2").Range("A1")
Worksheets("Sheet3").PrintOut
End If
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

You cold have it driven by the double-click event, so when ready to copy and
print, just double-click any cell in your target row

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

If Me.Cells(Target.Row, "A").Value <> "" Then

Me.Cells(Target.Row, "A").Resize(, 15).Copy _
Worksheets("Sheet2").Range("A1")
Worksheets("Sheet3").PrintPreview 'Out
End If
End Sub


PS delete the other code.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

Bob it works great, slight problem i can't select a cell and update it
without it printing. is there a way i could manually run the code after
i've
updated and selected the row?

 

[Bob Phillips]

Forgot to tidy up the code before posing

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

If Me.Cells(Target.Row, "A").Value <> "" Then

Me.Cells(Target.Row, "A").Resize(, 15).Copy _
Worksheets("Sheet2").Range("A1")
Worksheets("Sheet3").PrintOut
End If
End Sub



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JLatham]

Kirby,
Yes - put the body of the code into a regular Sub() in a module and do away
with the _SelectionChange() event code completely.

Open the VBA Editor (press [Alt]+[F11]) then choose Insert | Module to give
you a regular code module to work with. Create a 'stub' to put the code into
by typing something like
Sub PrintReport()
and pressing the [Enter] key. That will give you this in the code module:

Sub PrintReport()

End Sub

now just copy and paste the code in between the Sub...End area. You can
then choose the Macro from the Excel menu through Tools | Macro | Macros or
you can place a button on the worksheet that activates that sub when clicked.

Bob it works great, slight problem i can't select a cell and update it
without it printing. is there a way i could manually run the code after i've
updated and selected the row?

 

[Kirby]

Sorry for not responding sooner, but i work weekends. Anyways thank you both
Bob and JL for the help on this.
--
Thank you in advance
Kirby

Kirby,
Yes - put the body of the code into a regular Sub() in a module and do away
with the _SelectionChange() event code completely.

Open the VBA Editor (press [Alt]+[F11]) then choose Insert | Module to give
you a regular code module to work with. Create a 'stub' to put the code into
by typing something like
Sub PrintReport()
and pressing the [Enter] key. That will give you this in the code module:

Sub PrintReport()

End Sub

now just copy and paste the code in between the Sub...End area. You can
then choose the Macro from the Excel menu through Tools | Macro | Macros or
you can place a button on the worksheet that activates that sub when clicked.

Bob it works great, slight problem i can't select a cell and update it
without it printing. is there a way i could manually run the code after i've
updated and selected the row?