Macro - Too hard for me!!

[Guest]

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

 

[JulieD]

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

 

[R.VENKATARAMAN]

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

=========================

 

[Arvi Laanemets]

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