Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Macro - Too hard for me!!
Here is one for you experts - I am a novice
I want to add a macro to a worksheet that will print selected information held on certain rows of the worksheet. Once the 'button' has been selected a mesage box will pop up asking the user to input which row number he/she wishes to print. On input that number is matched to the corresponding row of the worksheet and that row is copied (without any formula - just cell values) and pasted into another worksheet where it is placed into other formats and printed. Seems like a simple(!!) job - but far to difficult for me to programme Can anybody offer some VB script?? Many thanks in advance Anthony |
#2
|
|||
|
|||
Hi Anthony
you could do something like this: Sub preparetoprint() Dim i As Long On Error GoTo err_handler i = InputBox("What is the number of the row you want to print?") If i 0 Then Sheets("Sheet3").Range("B2").Value = Sheets("Sheet2").Range("A" & i & "").Value Sheets("Sheet3").Range("C3").Value = Sheets("Sheet2").Range("B" & i & "").Value Sheets("Sheet3").Range("D4").Value = Sheets("Sheet2").Range("C" & i & "").Value Sheets("Sheet3").PrintPreview Exit Sub End If err_handler: MsgBox "An error has ocurred, please try again" End Sub --- where Sheet3 is your destination sheet and Sheet2 is the source sheet. Cheers JulieD "Anthony" wrote in message ... Here is one for you experts - I am a novice I want to add a macro to a worksheet that will print selected information held on certain rows of the worksheet. Once the 'button' has been selected a mesage box will pop up asking the user to input which row number he/she wishes to print. On input that number is matched to the corresponding row of the worksheet and that row is copied (without any formula - just cell values) and pasted into another worksheet where it is placed into other formats and printed. Seems like a simple(!!) job - but far to difficult for me to programme Can anybody offer some VB script?? Many thanks in advance Anthony |
#3
|
|||
|
|||
you mean you want to copy into the same row of another worksheet
if I understood correct try this sub Public Sub test() Dim i As Integer i = InputBox("type the row number desired") Worksheets("sheet1").Activate ActiveSheet.Cells(i, 1).EntireRow.Copy Destination _ :=Worksheets("sheet2").Cells(i, 1) End Sub ======================== experiment in a blank sheet type a few cells in row 6 of sheet1 when input window comes type <6 and click ok you will get the same data in row 6 of sheet 2 ========================= Anthony wrote in message ... Here is one for you experts - I am a novice I want to add a macro to a worksheet that will print selected information held on certain rows of the worksheet. Once the 'button' has been selected a mesage box will pop up asking the user to input which row number he/she wishes to print. On input that number is matched to the corresponding row of the worksheet and that row is copied (without any formula - just cell values) and pasted into another worksheet where it is placed into other formats and printed. Seems like a simple(!!) job - but far to difficult for me to programme Can anybody offer some VB script?? Many thanks in advance Anthony |
#4
|
|||
|
|||
Hi
No need for macro - you could do this with worksheet functions easily. An example. You have data on sheet MyData in range A2:X100 (A1:X1 are column headers). Add a sheet Lists. Into A2 enter the number 2, and until 100, fill down as series with step 1. Define the named range RowNum=Lists!$A$2:$A$100. Hide the sheet Lists. Add a sheet Report. Into A1 type "Row:". Activate B1, and from menu select Data.Validation. Set Allow to List with Source=RowNum. Now you can select row numbers to display (2 - 100), or type them (and only them) in. Into cell, you want to display the value from corresponding row from column A on MyData, enter the formula: =IF($B$1="","",OFFSET(MyData!$A$1,$B$1-1,0)) Into cell, you want to display the value from corresponding row from column B on MyData, enter the formula: =IF($B$1="","",OFFSET(MyData!$B$1,$B$1-1,0)) .... Into cell, you want to display the value from corresponding row from column X on MyData, enter the formula: =IF($B$1="","",OFFSET(MyData!$X$1,$B$1-1,0)) You can add any additional texts at your own choice, use different formats and colors, etc. **** When the column A on sheet MyData contains some unique identifier, then you can use it instead of row number - define the datarange in MyData!$A$2:$A$100 as named range, and use it as list source for data validation on Report sheet. Now you get other fields from this row using VLOOKUP function, like: =IF(ISERROR(VLOOKUP($B$1,MyData!A$2:$X$100,2,0))," ",VLOOKUP($B$1,MyData!A$2: $X$100,2,0)) to get the corresponding value from column B on sheet MyData. **** You also can use Word's Mail Merge with your Excel table as datasource instead. There are some additional possibilities in Mail Merge, plus all Word's text formatting options are available for you. With mail merge, you can determine the row(s) in table to process, or you can set conditional filters to your table and process rows which match them. Arvi Laanemets "Anthony" wrote in message ... Here is one for you experts - I am a novice I want to add a macro to a worksheet that will print selected information held on certain rows of the worksheet. Once the 'button' has been selected a mesage box will pop up asking the user to input which row number he/she wishes to print. On input that number is matched to the corresponding row of the worksheet and that row is copied (without any formula - just cell values) and pasted into another worksheet where it is placed into other formats and printed. Seems like a simple(!!) job - but far to difficult for me to programme Can anybody offer some VB script?? Many thanks in advance Anthony |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro - Too hard for me!! | Excel Discussion (Misc queries) | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) | |||
Date macro | Excel Discussion (Misc queries) | |||
Help with macro formula and variable | Excel Worksheet Functions | |||
Macro and If Statement | Excel Discussion (Misc queries) |