Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel macro question
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. -- Thank you in advance Kirby |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel macro question
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" wrote in message ... 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. -- Thank you in advance Kirby |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel macro question
Thanks Bob I'll give it a try
-- Thank you in advance Kirby "Bob Phillips" wrote: 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" wrote in message ... 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. -- Thank you in advance Kirby |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel macro question
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 "Bob Phillips" wrote: 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" wrote in message ... 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. -- Thank you in advance Kirby |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel macro question
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) "Kirby" wrote in message ... 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 "Bob Phillips" wrote: 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" wrote in message ... 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. -- Thank you in advance Kirby |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel macro question
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) "Bob Phillips" wrote in message ... 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) "Kirby" wrote in message ... 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 "Bob Phillips" wrote: 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" wrote in message ... 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. -- Thank you in advance Kirby |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel macro question
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. "Kirby" wrote: 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 "Bob Phillips" wrote: 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" wrote in message ... 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. -- Thank you in advance Kirby |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel macro question
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 "JLatham" wrote: 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. "Kirby" wrote: 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 "Bob Phillips" wrote: 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" wrote in message ... 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. -- Thank you in advance Kirby |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Macro Question | Excel Discussion (Misc queries) | |||
Excel Macro Question | Excel Discussion (Misc queries) | |||
Excel Macro question | Excel Discussion (Misc queries) | |||
Excel Macro Question? | Excel Discussion (Misc queries) | |||
Excel Macro Question | Excel Worksheet Functions |