Macro - Too hard for me!! #2

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
0
Anthony2219 (255)
2/27/2005 1:03:03 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
297 Views

Similar Articles

[PageSpeed] 20

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" <Anthony@discussions.microsoft.com> wrote in message 
news:31402191-5955-440F-96E3-0C2B39CE2EEE@microsoft.com...
> 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 


0
JulieD1 (2295)
2/27/2005 3:56:45 AM
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 <Anthony@discussions.microsoft.com> wrote in message
news:31402191-5955-440F-96E3-0C2B39CE2EEE@microsoft.com...
> 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


0
R
2/27/2005 3:58:33 AM
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" <Anthony@discussions.microsoft.com> wrote in message
news:31402191-5955-440F-96E3-0C2B39CE2EEE@microsoft.com...
> 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


0
2/27/2005 7:11:11 AM
Reply:

Similar Artilces:

re-install Money 2006 #2
How do I re-install 2006 without CD when I wipe my harddrive? I kept the file and re-installed in on the drive, but I am getting a Trial Version message that the trial period has expired -- but I purchesed the regular edition! Help!-- mmekeel In microsoft.public.money, mmekeel wrote: >How do I re-install 2006 without CD when I wipe my harddrive? I kept the >file and re-installed in on the drive, but I am getting a Trial Version >message that the trial period has expired -- but I purchesed the regular >edition! See http://support.microsoft.com/default.aspx?scid=kb;en-us...

delete personal macro
Hi, I've created a macro in my personal wkbk,however, I dont know how to delete the macro completely. I tried to delete from tool --> macro.... , a pop up says I hav to unhide a wkbk to perform. For this, I dont really understand which workbk its asking actually? The orginal one when I was creating the marco? thx The workbook to unhide is Personal.xls. You do that from the Window menu. /On Mon, 21 Mar 2005 18:15:04 -0800, "kalz" <kalz@discussions.microsoft.com> wrote: >Hi, >I've created a macro in my personal wkbk,however, I dont know how to delete &g...

Disabled Exchange 2K3 accounts starting to accept new emails #2
Hello, whenever I would disable a user account in Exchange 2003, it would NDR any new email, unless there was a forwarding address in the delivery options tab, which is how I wanted it to be. However, since applying the security updates for February, the disabled accounts have begun accepting new mail. Does anyone have any ideas why? Feb. updates applied: KB890830 KB931978 KB926436 KB924667 KB928843 KB928090 KB918118 KB931836 Hi, you are right, and I think that Microsoft want it to be that way from the beginning. Read this articles http://support.microsoft.com/kb/319047 http://support.m...

Time series #2
I have several tabs for different dates with data in it. Colomn contains the time in format 13:30:30. For my project, I require to know the number of data points in variou time series, pls see below. 7:30:00 8:30:00 8:30:00 9:30:00 9:30:00 10:30:00 10:30:00 11:30:00 11:30:00 12:30:00 12:30:00 13:30:00 13:30:00 14:30:00 14:30:00 15:30:00 15:30:00 16:00:00 I tried countif and if function but could not come up with the results PLS HELP. If I have to do it manually then I am dead..... -- mohitmahaja ----------------------------------------------------------------------- mohitmahajan's ...

Errors #2
In the accounts list the balance shown on my checking account is 287.24 less than what it is on the check register. The check register is correct. The only check of that ammount was written in 2002 if that means anything. I am using Money 2004 Standard on a new notebook. The error started after the first days of software use. I have run both fix programs and nothing repaired. Any help would be appreciated. In microsoft.public.money, JamesLG wrote: >In the accounts list the balance shown on my checking >account is 287.24 less than what it is on the check >register. Th...

New Message Notification #2
I use Outlook 2003. When a new email message arrives, I want either to have an icon show up in my system tray or a box show up on my screen (you've got mail). I have checked the box for the icon to appear, but nothing happens. I haven't found an option to choose for the box to appear - and remain - on my screen. Any ideas? Thanks. Ken, In Outlook 2003, you can go to Tools-> Options -> E-mail Options -> Advanced E-Mail Options to find settings on e-mail notification. You can play a sound, briefly change the mouse cursor, show an envelope in the notification area, an...

Can't Assign Macro (not grayed out but nothing happens)
This is not the case where the "Assign Macro" option is missing. My Solution: Be sure at least one worksheet is open. Symptoms: Right Click on Toolbar Click Customize Right click on Command Button or Menu Item, etc. The "Assign Macro" option is there but nothing happens: Hopefully this will help someone. -- My handle should tell you enough about me. I am not an MVP, expert, guru, etc. but I do like to help. ...

Excel 2007 Bar and Line chart on 2 Axes
I used Excel 2003 to create a "bar and line chart on 2 axes" and now have 2007. I can't find where or how to create these charts using Excel 2007. Can someone tell me how to do it? Start here http://office.microsoft.com/en-us/excel/HA102004991033.aspx Then Google "Excel 2007 chart"; look at a few sites Then come back here with specific questions and we will help you get it right. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Nancy" <Nancy@discussions.microsoft.com> wrote in message...

Excel 97 worksheet with macros won't open in Excel 2000
I've got a workbook saved in Excel 97 that has some embedded macros (not sure where-we don't really need them). When trying to open from Excel 2000, get error message "An error occurred while initializing the VBA libraries (126)" and the workbook won't open. Any ideas on how to make this work in 2000? Or maybe even find where the macros are and get rid of them? Thanks, Marion ...

e-mail 2 reports
Is it possible to email 2 reports as 2 separate attachments? With outlook as an email client: http://www.datastrat.com/Code/OutlookEmail.txt -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Samuel" <samuel.shulman@ntlworld.com> wrote in message news:%23fzywcCtHHA.4236@TK2MSFTNGP05.phx.gbl... > Is it possible to email 2 reports as 2 separate attachments? On Jun 21, 2:07 pm, "Arvin Meyer [MVP]" <a...@m.com> wrote: > With outlook as an email client: > > http://www.datastrat.com/Code/Outloo...

Can 2 Union Queries Be used in Another UNION ??
I have created 2 separate Union Queries that work just fine. Can these 2 union queries be used in Another Union query (that will essentially combine the output of those 2)? Thanks very much, Kev -- Message posted via http://www.accessmonster.com Yes, as long as the two union queries are outputting data in the same structure and as long as the query engine doesn't decide that the combination of the union queries is too complex. Have you tried to do this? If so, did it fail? And if it failed were there any error messages? -- John Spencer Access MVP 2002-2005, 2007 Center for H...

Repeating instructions in a Macro.
Hello all, I have a Macro that clears any data on different 31 tabs and, due to my lack of knowledge, I do this one tab at a time. The 31 tabs represent the maximum possible days in a month. Here's a sample of my Macro: '====================================================================================================================== ' Clear the data on tab 1. Windows("Revenue Tracker.xls").Activate Sheets("1").Select Range("B4:O126").Select Selection.ClearContents Range("E4").Select '-----------------...

macro results wrong
I recorded marcos in a workbook. I then saved the workbook uder a ne name . Naturauly I had to modify the macro ref file names to the new fil name. I can now run the macros , however I get the wrong results. example: i have this formular =if(data!c5=0,0,data!c5/data!d5) in the orignal workbook it works fine when I copy an autofill the fields in that column. In the new workbook the formular is right but th resutant value is a constant for all the auto filled cells!! eg. if th cell A1 = 3 all the other cells A2 , A3 , A4 are dispalying 3 enven the formula shows otherwise. Please help :co...

Excel / VBA
Hi, I am trying to find how to add or remove the description displayed on the "Macro" pop-up menu (Alt-F8). Generally macros recorded have a banale description giving the time/date & username, others that I write directly in the VBE have none. Can anyone point how to add/modify this description? thanks, Scott --- Message posted from http://www.ExcelForum.com/ Scott, When the macro recorder fires up, the first dialog allows you to put whatever description you want in, including none if so desired. The value gets saved as comments, but changing the comments does not chang...

Switchboard hangs when running a macro
Access 2003: I've recently discovered the Switchboard - a grand concept; wish it wasn't so hidden... When running a macro from a form, no problem. But when running the same macro from the Switchboard, Access hangs. It seems to run all of the steps in the macro, and finish, but then Access hangs. Any help would be appreciated. Thanks, Mike in Sugar Land, TX On Mon, 30 Nov 2009 21:28:03 -0800, Switchboard newbie <Switchboard newbie@discussions.microsoft.com> wrote: My crystal ball is broken. Can you post some code, stripped to the bare essentials? -Tom. ...

Converting Crystal report from MSCRM 1.2
I have a client who has upgraded to V3.0 for CRM. They want to continue using Crystal for their reporting engine because they have 40 custom reports. Does anyone know how to easily accomplish this? I have created a new connection to the new CRM Views in SQL, but I can't seem to figure out how to easily replace all the fields that are currently in the report using the SSO connection to my new connection. Any help would be greatly appreciated. Thanks, Amy many of the services such as rpttosql.com say they will only convert so far. some even say they will only get the general lay...

inbox repair #2
vikash@rfl.co.za <anonymous@discussions.microsoft.com> wrote: <blank message> And the question is??? -- Brian Tillman ...

Trigger Macro on Open Spreadsheet
How can we force a macro to execute whenever a spreadsheet is first opened? Hi do you mean the file? If yes either name your macro Auto_Open() or put your code in the workbook event Workbook_Open() -- Regards Frank Kabel Frankfurt, Germany Beckie Davis wrote: > How can we force a macro to execute whenever a spreadsheet > is first opened? ...

refund check #2
I have multiple customers requesting that the Refund Checks module be able to create Manual Payment batches in Payables Management. Right now Refund Checks only creates Computer Check batches. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/B...

2 Formatting Questions
Date Fields: Can we, in the CRM forms, format dates to read into the “15 Aug 2005” format? This is possible in reports, but by default, the forms use only numbers for dates, but in any format. Currency: Any ideas of how to track multiple currencies in the same application - specifically USD and Canadian? I know a Microsoft CRM installation currently supports one language. But aside from using a picklist to specify "USD", "CAN", "MEX", or other I'm concerned about how their forecasting. -- Carroll Little Vis.align, LLC 610-692-3290 x3326 www.visalign.c...

Outlook 2007 adding contacts issue #2
Hello! I apologize in advance if this has been answered, but I couldn't find it anywhere... today I added contacts to outlook 2007...never had used it before on Vista ever...and closed out went to lunch... came back, I cannot add any new contacts. All options to add a greyed out... If I select file new.....adding contacts is there but greyed out... any ideas on what to change? Thanks! lori, Go to your contacts page and type CTRL + N. The new contact page will open. Or on your contacts page, click New on the button that appears just below the File menu item. Or on your contacts pa...

Solver Add-in #2
I have an Excel File (around 4.5 megs) with 28 sheets. using the Solver add-in on the NPV function subject to a few restricting criteria. Max iterations 100 Max Change 0.01 under the Tools --> Calculation menu. Now when I launch Excel for the first time, I can click Tools --> Solver and see the conditions et al. but exiting from the Solver once and then later on trying to reaccess it through the same Tools --> Solver the Solver dialog does not come up. I have to quit excel and restart it again to view the Solver dialog. What could be going wrong here? Any ideas/suggestions? No ...

Macro or VBA
I am dumb, but would like to automatically trim down reports that we get from our IT dept. We have to delete redundant columns (A,B,C,D,E,G,H,J,K)from each report. In other words, the only data we want to view is in F,I,J,P. Can this done more effciently with a macro or VBA/ Could someone give me an example of either so that I could work from and learn? Thank-You start a new workbook open one of those report workbooks record a macro into that new workbook (not the report workbook) when you delete the columns you don't want. stop recording back to that new workbook save it as a nice...

Timesheet Problem #2
I am trying to populate a timesheet with dates depending on the start date in B2. If B2 is the 16th of the month of Sept, I don't want anything to show up on the calendar for the 31th (blank in other words). Here is the formula I am using, but not working. Help. TIA =IF(($B$2)="","31",IF(EOMONTH(B2,0)=TEXT(($B$2)+ROW()-14,"DDD dd"),"",TEXT(($B$2)+ROW()-14,"DDD dd"))) Greg Hi Greg, With seed date in cell $B$2 and using rows down to leave the 31st blank. But show all other dates no exceptions for weekends and holidays. EOMONTH is in ...

dates in excel #2
hi all need help with the date function this is the situation i use excel daily to create a report. as this report is printed out i just save changes made. except at the weekend when i also make backups so that my boss can send the report by email on monday mornings. in the report we use the NOW function to display the date. when we used to open the backups the date never used to change ie if saturdays backup was opened on monday morning it used to still display saturdays date. now for some reason the date is automatically updating. anybody know how to prevent this happening You probabl...