Macro To Print Active Cell And 6 Rows 4 Col
I have a workbook that need a macro to print current active cell
and the surrounding 6 rows 4 cols.
Also the selection printed need to fit
to page pref landscape on printing
the active cell varies day to
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
If Cell In Col. A Exists As Part Of String In Col.B Then Color It
i have two columns in my worksheet A and B. Now col. A contains of words and column B contains of long strings. I want to make those parts of col. B which match to the content of A. For Example It is now: Col. A...................Col.B word....................öladföljadfwordpapdfadlk word2..................öadsfoewordpasdfword2rs Should be: Col. A...................Col.B word....................öladföljadfwordpapdfadlk word2..................öadsfoewordpasdfword2rs I am attaching this example as excel file. I will be very thankful if some one could add the formula to this file and upload it.
View Replies!
View Related
Print Active Cell Highlight
I have a routine that highlights a selected Active Cell, but when I printout the spreadsheet this highlight is not printed. I have been pull my hair out (what's left!), to try to show this highlighted cell when printed. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Intersect(Range("d22:u45"), Target) Is Nothing Then Range("d12").Value = " Out" Range("e14").Value = "0" Else If Intersect(Range("d22:u45"), Target) >= 3 Then Range("d12").Value = Cells(19, ActiveCell.Column).Value Range("e14").Value = Cells(21, ActiveCell.Column).Value Static rngPrev As Range, PrevColor As Integer Dim TempColor As Integer TempColor = Target.Cells(1, 1).Interior.ColorIndex If Not rngPrev Is Nothing Then If PrevColor <> xlLineStyleNone Then .......................
View Replies!
View Related
Copying A Selected Cell In Col A And The Next Cell In Col B Into New Sheet
Basically I have a filtered list in colunms A to D. The user selects a cell in column A and I want to then be able to run a macro which copies that cell and the adjacent one in column B, into columns A and B on sheet2. The complications are Column B on the second sheet is merged with columns D to E so a straight forward cut and paste of A and B on sheet 1 into A and B on sheet 2 will not work in one cut and paste as it complains about merged cells. It would need to copy and paste A and then copy and paste B to avoid the error. The row on sheet 2 where it is being pasted needs to be the first row between row 10 and row 30 where col A is blank (so it doesnt overwrite a previous copy and paste as they are basically creating a list on sheet 2 in those rows by runing this macro repeatedly). if there is no blank row then it should insert a new row at row 10 and paste into that
View Replies!
View Related
Macro To Print Active Area Excluding Cells With Forumula That Doesn't Return Value
Trying to put together a macro that looks down active sheet for all cells that contain a value, sets a print area and then prints ! Is this possible? FYG, I have a column that run from 3 - 2000, which contains a formula, which may produce a value depending on corresponding cells. I used this code from a post on a similar topic, but excel is complaining code in bold Private Sub Print_Area_Click() Dim lastCell As Range Set lastCell = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0) Do Until Application.Count(lastCell.EntireRow) 0 Set lastCell = lastCell.Offset(-1, 0) Loop ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), lastCell).Address End Sub
View Replies!
View Related
Hide/unhide Col Macro Based On Cell Value
I have a worksheet that contains 10 columns of data. In row 2 I have a formula that will display the result "x" if the data in that column should NOT be hidden, and for all columns of data without an "x" in row 2 I would like to hide the entire column. Therefore, I am looking for a macro that looks across the range of cells E2:N2 and if there is not an "x" in the cell, then hide that column... To make things a little more complicated, the value in row 2 will change when other values are amended on other worksheets that feed into this one and I will need the macro to 'unhide' the column as soon an "x" appears in row 2
View Replies!
View Related
Macro To Print Page Based On Active Page
I need a macro that will print a hidden page that is linked to the page I have active. It needs to be able to find the correct hidden page that corresponds to the active page because there are multilple pairs of the hidden/visible pages. The name of the hidden page is the same as the visible one with "printout" added to the end. When created, the sheet code names are sheetn and sheetm; n and m being consecutive numbers.
View Replies!
View Related
Macro To Print Rows That Keep Increasing?
I'm searching for a macro that will allow me to print rows that are constantly changing in number. Attached is a sample of my workbook. The workbook has worksheets by month. There are data from five sales people on each sheet so each sales person has his own section. I have a print button within each section so he can print only his section of the page. No problem creating that macro. However, at least twice a week the sales people are adding rows or moving a row of data from one month to another month, so the print area is constantly changing.
View Replies!
View Related
VBA - Code- Send Worksheets Named In Col B To Email Addresses In Col A
I have a code that i got from here which will send the Workbooks named in col A to email addresses in col B, as below.. but i need it to send the named Worksheets from the currently active Workbook. The name of the Worksheet will be contained in col A. Sub SendWkbs() Application.ScreenUpdating = False Dim wks As Worksheet Dim iRowA As Integer, iRowB As Integer Set wks = ActiveSheet iRowA = 2 On Error GoTo EH Do Until IsEmpty(wks.Cells(iRowA, 1)) iRowB = 2
View Replies!
View Related
Count Names In One Col If 2nd Col Isn't Blank
I've got one column with names in it and a second column with dates in it. What I'm trying to do is generate a count in a third column next to each name with the number of times the name appears in the name column when there is a date in the corresponding date column. If the date column is blank I want the count column to remain blank. For example: Assigned To: Number Completed Date Joe 2 1/1/06 Jane Joe 2 2/22/06 I know that there will be repeated info but the end user doesn't care.
View Replies!
View Related
Insert Rows At The Active Cell
I want to be able to allow a user to insert a number of rows (they would specify the number) into a worksheet, at the active cell location. I also need the same number of rows to be inserted into the same worksheet (but much lower down) The location of which is to be governed by the location of the original inserted rows. Having created the new rows I need all the formulas from the row ABOVE the active cell to be copied into all the newly created rows. example If active cell is A5 I want user to be able to run a macro which asks them how many rows they wish to insert. (Assume the user asks for 7). The macro then inserts that number of rows, (starting at row 5). And additionally adds 7 rows at a location further down the worksheet (say row 105). The formulas from the row above the active cell are then copied into all the newly created rows. 2nd example If the active cell was A9 then the macro would add 7 rows, starting at row 9 and additionally add 7 rows starting at row 109. Then formulas added.
View Replies!
View Related
Delete N Rows From Active Cell
Is there a quick way to delete "N" rows from the active cell regardless of the content of the cells in those rows? I import text files into excel then frequently have to go through them and delete rows of nonimportant data. For example if cell A57 is currently selected can I run a macro that will delete the next 10 rows?
View Replies!
View Related
If & And Formula: Show The Difference Between Col F And Col G
if the cells in column E AND column F are blank, then column G will be blank also (I dont want 0 showing), if not show me the difference between col F and col G. I put in the following:- =IF((E2="")AND(F2=""),"",E2-F2) However, Excel informs me this is wrong and corrected it as =IF((E2="")*AND(F2=""),"",E2-F2) I guess my question is why do I need to put in "*" for this to work?
View Replies!
View Related
Macro To Set Print Area W/o Hidden Rows
We run a small gardening shop and use a simple spreadsheet to track of various things. Row A contains headers, with the data following in the rows underneath. This table is now quite large, and we therefore hide 600 or so rows so that only rows with data from the last week is shown. We often need to print this for easy reference. I currently do this by highlighting the area I want and setting it as print area. The print therefore doesn't include hidden cells, which is what I need. The problem we have, therefore, is that we have to manually select this print area each time. My experience with macros is VERY limited, to the point where I can record one which will select the print area and print. However, as the list gets longer and more rows are hidden the range obviously needs to change, and my simple macro will not keep up. So my question is as follows. Is there a simple macro I could write to assign to a button that could "keep up" with the moving range? So either it always prints the header row and the, for example, 50 rows beneath (exlcuding the hidden ones), or, even better, it prints the header row and all rows with data in them that are relevant to the last week. Each row has a cell for the date it concerns so assume this may be possible?
View Replies!
View Related
Auto Fill Value For Col. C Dependent On Value From Col. B
I have a spread sheet which tracks work we do on certain items. These items (about 200 of them) consist of a 3 or 4 number tracking code (they are all 4 numbers, but some have leading 0's.) This tracking code is entered into Column B and when we cross reference the number to a chart we have we can determine if item is either, "model A," or, "model B." I want to get rid of that cross reference chart, or rather, I want excel to cross reference for me and automatically fill A or B into Column C, right next to the tracking code. Is this possible without VBA? It seems like it may be something simple, but i've poked around and have done nothing but make a mess.
View Replies!
View Related
Remove Duplicates From Col.A And Merge Col.B
i have a wordlist containing about 0.5 Million words in col.A and Millions of meanings in Col.B. Now in Col. A there are many duplicates. I would like to make one cell of all duplicates in col.A and put their meanings together in B with seperator ";". Its very important that there are no duplicates in Col. B. For example: Col. A Col.B w1 m1;m2;m3 w1 m1;m2;m5 w2 m23;m24 w2 m50 Now after running the macro the list should look like: Col.A Col.B w1 m1;m2;m3;m5 w2 m23;m24;m50 The words in col. A are alphabetically ordered and all the duplicates are followed by each other. .
View Replies!
View Related
Range X Rows & X Columns From Active Cell
Im writing a macro and have a cell activated (using ActiveCell). If I want to select this cell (the activated one - i.e., k3) along with the 2 cells next to it (i.e., l3, and m3), how would i go about doing that? I'm wanting to shift 3 cells downward and I know how to shift them down, but dont know how to select the cells i want.
View Replies!
View Related
Remove Duplicate Rows Based On Col C
I am using three columns - Col A contains serial number, col B contains name and Col C contains Codes. The problem is in col C as sometimes the codes are copied from other files also. So I want that if there is any duplicate codes in column C, on running the macro it will delete the duplicate rows and keep original record ie the duplicte records added later has to be deleted and that the original record will be in place.
View Replies!
View Related
Only Print Active Sheets
Is there any way to only print active sheets i mean, i have 4 sheets in my workbook, and if i only use one sheet i dont want to print all 4 sheets i would like to print sheet 1 if cell D19 has data in it i would like to print sheet 2 if cell D61 has data in it i would like to print sheet 3 if cell D103 has data in it i would like to print sheet 4 if cell D145 has data in it
View Replies!
View Related
How Do I Delete Rows If Time Diff Is Less Than 3 Mins In Col
I have a workbook of which has different times in columns D and E. What im trying to do is where the time difference between the columns is LESS than 5 mins I need that row deleting and cells shifting up. I.E lets say D14 is 05.24 and E14 is 05.50. I need that line keeping but if E14 was lets say 05.27 then I need that line deleting and cells shifting up. I need to be able to do this right down the workbook until the last entry.
View Replies!
View Related
Remove Entries In Col A That Appear In Col C
Hello all, I was wondering if anyone would be able to help with a macro or even formula that would remove the entries in Col A that appear in Col C. Attached is an example with dummy data, Col D is how the list should be. If a number appears in A but not C then all instances of it should be kept, conversely if it is in C but not A then it does not need to be kept. Most importantly I need to be able to delete those that appear in both by the same number of times they appear in C. Hope this all makes sense. Please feel free to ask if it is not clear.
View Replies!
View Related
Automatic Value In Col E Based On Col C.
Is it possible to have Column E automatically enter a value based on a 4 digit number inserted into Column C? I have about 200 different 4 digit numbers that can be entered into Column C and 8 different corresponding values that need to appear in Col E... Would this be possible / difficult?
View Replies!
View Related
Copy Col B Data To Col A
i have 2 colume data in excel col A , with G1, G2 , G3 , and empty cell. i need to a macro to search for Col A , those empty cell, and fill the value from col B. before :- C1 C2 1 G1 2 3 G3 1 2 2 G2 2 G2 After C1 C2 1 G1 2 2 3 G3 1 1 2 2 2 G2 2 G2
View Replies!
View Related
Print Out Data That Is Variable In The Amount Of Rows To Print
I am trying to come up with a way to print out data that is variable in the amount of rows to print. 1. Cells C1 thru M6 is heading of report 2. Cells C7 thru M400 all have formulas and display information only when criteria in Column C in each row is met, if condition is not met it displays no informtion. (There lies my problem). I need a way to print out only the area that information is displayed in and skip the areas that are not displayed. (Currently I have hard coded the print range using the largest report)
View Replies!
View Related
Can A Macro Start From Wherever Cell The Active Cursor Is On
Can a macro start from wherever cell the active cursor is on? or perhaps from the cell that the macro button is clicked? What I am trying to do is copy the data from F(whatever) to whatever the last empty cell is in the row holds the active cursor. Or I can create buttons for the macro to run, and it could copy the information in that particular row.
View Replies!
View Related
Return To Active Cell After Macro Run
I am using Excel to tabulate scores for my employees. We work in a very busy and open office, so there is a need to be able to obfuscate the scores, but also help keep from losing my place while punching the scores. I would like it to be able to return to the last cell that I was punching a score in...I used "ActiveCell.SpecialCells(xlLastCell).Select". I have also used " x= cells(Rows.count,2).end(xlUP).row" followed by "cells(x+1,2).select", but both with no luck... Sub Hide_Scores() Range("B15:EU35").Select ActiveSheet. Unprotect With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0
View Replies!
View Related
Insert Two Columns To The Right From Col H, Add Header Value "Ledger" In Col I1
I need a macro that will insert 2 columns to the right from Col H, add header value "Ledger" in Col I1 and "Statement" in Col J1. I then need the macro to put the values for ledger and statement next to each other as they are both related (as per below screen shot). I have highlighted which units are related so you can see that all related entries will have Ledg followed by Stmt and if they are not related then will be inputted under their relevant headings. I then want the macro to delete rows which have blank cells. Ledg StmtUnitsLedgerStatementLedg389,351.00389,351.00599,811.00Stmt599,811.00Ledg148,822.00148,822.00209,536.00Stmt209,536.00Ledg1,674,562.001,674,562.001,661,564.00Stmt1,661,564.00Stmt111,110.000.00111,110.00Stmt7,738.000.007,738.00Ledg39,164.0039,164.000.00Ledg197,088.00197,088.000.00Ledg195,525.00195,525.000.00Ledg8,859.008,859.008,860.00Stmt8,860.00Ledg90,000.0090,000.00138,648.00Stmt138,648.00Ledg717717.000.00Ledg11.000.00Ledg5,314,520.005,314,520.000.00Ledg2,096,835.002,096,835.002,402,199.00Stmt2,402,199.00Ledg114,797.00114,797.00193,797.00Stmt193,797.00
View Replies!
View Related
Macro: Look At The Last Code In Col And Apply To Next Sequence
Step 1. In Col E a list of amounts will be pasted on a daily basis. Step2. I need a macro that will look at the last code in Col F and apply the next sequence so $36,543.00 will have OPS003, since i might paste more than one amount in Col E i would like the macro to do the same thing also in Col F. I have a formula in G which will tell me what amounts are outstanding and which have cleared. AmountCode 12,545.00 OPS001 1,236.00 OPS002 36,543.00
View Replies!
View Related
Macro To Print If Cell Value Is Match
I have someone to setup a macro for me to print from 1 to 100, but I would like to set a criteria print only if the cell(A1) = "A" then run the macro. How can I modify this macro? For a = 1 To 100 Range("C1").Select Selection.Copy Range("B5").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Next a
View Replies!
View Related
Date, Cell Selection And Print Macro
There's a macro I'm trying to figure out for a calendar I'm working on. Here's what it has to do. 1.) Selects the cells for the current month (I assume the user would have to click on the current month itself) plus the two upcoming months. i.e. November, plus December and January. 2.) Sets the selected cells as the area to print, then prints those cells. If there's anything that might be a challenge, the numbers for the days are all text boxes, if that makes a difference. I've been experimenting with different formulas with no luck.
View Replies!
View Related
Stop Print Macro Firing Before Print Event
I have a "submit" button macro which user would click after he has finished his input. This macro would update a reference number on the worksheet named "orange" and then print out this worksheet. My purpose is "orange" is printed out with a reference number. Below is the 1st code.....
View Replies!
View Related
Autofilter Col A,copy Col C To New Sheet, Name New Sheet With Criteria Value
In my "MAIN" Sheet. Column A starts at 12th row and contains ONLY Strings. I would like to Autofilter Column A for every unique String (criteria) in Column A (from 12th row to 1000th row). Copy the thus filtered (i.e. visible) cells from (ONLY) column C (for every criteria). Paste the copied cells to a new sheet, starting from A4 of the new sheet. Give the name of the Criteria as the name for the new sheet. Come back to "MAIN" sheet and show a pop up "Sorted".
View Replies!
View Related
Vba To Unhide Rows, Print, And Rehide Rows
I am attempting unhide the same exact rows in multiple worksheets in Excel, Print the entire workbook, and then re-hide the same cells. As I am still learning VB i have been unsuccesfull. I have attached the code that I am using, Sub Printdoc() Dim sh As Worksheet For Each sh In ThisWorkbook.Worksheets With sh Rows("105:116").Select Range("A105").Activate Selection.EntireRow.Hidden = False .PrintOut Rows("105:116").Select Range("A105").Activate Selection.EntireRow.Hidden = True End With Next sh End Sub
View Replies!
View Related
Disable Print But Allow Print From Macro
I have found a macro which disables all print features in excel. I have rwo other codes to print. When I put this macro, It also disables the print from the other macro. How can I use this code so that normal print features will be disabled but if someone takes the print from the macros, Print will be allowed. The code which disables the print is given below: Private Sub Workbook_BeforePrint(Cancel As Boolean) If PrtOK Then Cancel = False Else MsgBox "Can't print from here!" Cancel = True End If End Sub
View Replies!
View Related
Option To Print - Use Print Macro
I have a few macros that run in a sheet. After the macros are finnished I would like to present to the user a msgbox that asks if they would like to print. if "Yes" is selected, the print macro runs. If "No" is pressed then nothing happens.
View Replies!
View Related
Reduce Number Of Active Rows In A Worksheet
i have data in 1 worksheet say sheet1 which i copy to another sheet say sheet2. i do some processing like using some formulas on the data from columns A to D and getting an output in column E in the same sheet. Now the problem is, before i start the calculations in sheet2, the file size is 400 kB... and after the calculations are done, it becomes 20 mB... i dont know why this happens... there are some things which i think might be a reason but not very sure... 1. i calculate the last row in column A using lastrow = Range("A65536").End(xlUp).row i tried Range("A500") and it seemed fine... Now u may tell me that i can just use 500 and make things simpler but its just a workaround and not a solution... 2. i use macros.. this is just an info... 3. Another thing is, if i use 500, the number of active rows is 500... i dont know whether the right word is active rows but wat im trying to say is, the scroll bar for the rows movement reaches row 500 when it reaches the bottom.
View Replies!
View Related
Extract All Matching Rows With Active From A Database
Heres what I need: On the 'values' worksheet (first one), a formula in cell M2 that will search the worksheet 'oc_users' (second one) for any row containing "Active" in column G, these rows will return the corresponding email address in column A of the same worksheet. Skipping "Inactive" rows. End result: anyone with an active status in oc_users will have their email populated in the 'values' worksheet.
View Replies!
View Related
Loop Instructions For Number Of Active Rows
First: I am using following loop code to repeat until the last row with data in column B has been reached. I keep getting a compile error and can't figure out to to fix it. The compile error occurs on the ".cell" and ".range" portion of the code and has indicated an invalid or unqualified reference. Second: Once I have the loop working, I need to reference different cells each time through the loop. The first time through the loop I need to reference cells B2 and C2....the second time cells B3 and C3.....and then B4 and C4....and so on. I am increasing the value of i each time but when I ran the code, I was not getting any results.
View Replies!
View Related
|
TRACKER
