I have the following macro that works fine. But, I would like it to stop when it gets to the bottom of the active area.
Sub Insert_for_SQs()
Do
Selection.EntireRow.Insert
ActiveCell.Offset(6, 0).EntireRow.Activate
Selection.EntireRow.Insert
ActiveCell.Offset(12, 0).EntireRow.Activate
Loop
End Sub
I have various lists of data (using Data > List > Create List...) that do not always have the first cell in the list in column A. I have spent quite a bit of time searching the internet to try and find a macro that will work the same as... Data > Sort > Sort By: Col xx > Then by: Col xy.
Dave supplied me with a macro that will sort a range by the first column:
Sub SortThis()
With ActiveCell. CurrentRegion
.Sort Key1:=. Cells(1, 1), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
End Sub
I have recorded macro that does the same and looked at the code but this uses Range and specifies the cells to be sorted. I have not been able to work out how to modify the code Dave posted to do a minor sort by the second column. I am after a macro that will determine the range (could be a varying number of columns and rows in different places). For however many columns there may be the macro will sort the full width and height by the first column and then the second column, with all the data within a particular row not being split.
I am trying to write a macro that publishes the print area as a non-interactive web-page to a file that sits in the same location as the spreadsheet from which the macro is being run.
I am using the the following macro:
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
How can I print 2 selected area (highlighted area). I know there is a trick to do that. Right now I can only select (highlight) one area to print, but would like to know how to print 2 or more areas at same time
View 7 Replies View RelatedUsing Excel 2003 I am trying to write a macro to set the print area according to the amount of data in a particular range of cells. I find I can include this instruction
View 2 Replies View RelatedI am trying to graph the following data in an area chart with line charts superimposed on the area chart. I have a lot of data (and a lot going on) so I'm trying to figure out the best way to show this in excel from a functional standpoing (i can't get this to work in excel!!) to also an asthetic standpoint (dont want it to look terrible or illegible). This is what I'm trying to chart:
1) Weather data (temperature) by region:
So one region, would be: Northwest
I would like the "area" (so a shaded region) to be the min/max of the temperature data for each month.
2) I would like to show the temperature for each year as a line graph on the chart - so you can see if a year falls in or out of the shaded region.
3) I would like to show a company's sales increases across the same months per year as separate line charts. I may choose to just show the biggest outlier year in the end... or to show 2006 (the latest data).
What I am trying to convery with the chart is that the company's sales is or is not tied to weather deviations. I have attached an excel file with the data. I haven't been able to use the area chart or get a two axis chart to work or get it to look even remotely professional.
I have a workbook with multiple sheets that I need to print.
Is there a way to create a fixed print area within which you can mess around with formatting without extending or shrinking the print area?
I have tried adjusting the margins settings so that they are all the same, but this does nothing to keep a fixed print area.
I have a range of amounts in Sheet 1 from F7:Q13 and im using the find method to search for the active and non active values in the cell. Which means that if there's a value in the cell it will transfer the value in Sheet 2, if nothing is found in the cell the cells in Sheet 2 will return as nothing or null.
I think the problem lies on the FindWhat variable. Im getting a compiled error which im not sure what is it.
I've attached the spreadsheet so you get a better idea of the problem that i encountered.
I currently have the following macro running to set a chart's data values:
Sub C3Quarter12013()
'
' C3Quarter32013 Macro
'
'
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Values = _
[Code] ......
When I copy the tab and change some of the data within the cells, I want the macro refer to the chart on the current tab and the values in the current tab - as currently it refers to only "Chart 2" and the values in the tab 'Figure 2 - WE OPH'.
I've tried changing the sheet name to ActiveSheet.name but that doesn't seem to work.
I have got stuck on one piece of my code and having trouble fixing... Overall I am trying to find variable station name in cell L2 of Sheet 2 in Sheet 1 and then select and copy the data from the data in "cell L2 of Sheet 2" to the last entry of that row. I have attached an example test spreadsheet of the data and a macro is within Sheet 1 called test1. Please note that cell L2 in Sheet 2 will always be different station name and the station list in Sheet 1 will change with differing station name.
The code I am using is:
[Code] .....
The code that is not working and bringing up an error is:
[Code] .....
Attached File : Copy of Testexample.xlsm
whenever the active cell is within a given range, highlight the cell on the same row in column S (by changing its interior colour). This should occur each time the active cell is changed, whether by cursor keys or mouse. The effect would be similar to the row and column highlights at left and top of the worksheet.
This action should be restricted to one sheet in the workbook.
It's for Excel 2003.
i want to do a sum of values, based on 2 criterias, one is that it has to match the name, and it has to match another criteria say "Y" in this matter.
in one sheet i have
row 1 to 100, column a:
list of names of people
row 1 to 100 column b:
list of "Y" or "N"
in row 200+, column a:
list of names of people
in row 200+, column b:
values
I need to sum the values in column b, if the name in row 1-100 is the same as row 200+, and the criteria is "Y" for that particular person, as indicated in column b row 1-100.
I have a sheet that will be about 5 pages long if printed. To minimize wasted ink and paper, using VB I would like to be able to have it only print the portion uses (data entered) along with a section at the top and bottom. The section at the top would print on the top of the first page and the section at the bottom would print on the last page.
I know I could record a macro to select an area and print that but it would not do what I am trying to accomplish.
1)I am trying to specify the area of a worksheet, for example columns a-z and rows 1-1000 but I am having great difficulty in doing this. I have tried selecting the last row and shift>end>down, Edit >all then Edit>delete but nothing happens. The screen just flickers and all the rows are still there. Even specifying the print area does not do it. Any answers?
2) Any ideas how to sum a datalist by text in a box? I.e if there are 1000 rows selected how amny are filled. If there is a way of selecting 'any text' this would work but there is no constant value/text to use.
BTW I am using Excel 2000
I have an excel sheet with lots of rows (it goes up to BAA) I would like to be able to print certain parts with a VBA
for instance if Sales is selected from a drop down (lets say A1) the macro will print
Area : C7:Z500
Scaling: fit to 1 page
Orientation: landscape
Color: Color
or if Sales next year is selected
Area : C7:D7 AA7:AZ500
Scaling: fit to 1 page
Orientation: landscape
Color: Color
etc....
I use the code below to print a portion of a sheet. My problem is that the area always changes. The $A$476 should be the first cell in that column that contains "HEA" The "536" in $F$536 is the last row that contains data. There are conditional formats all the way down to row 1,000 but not data
Private Sub CommandButton1_Click()
Dim strCurrentPrinter As String, strNetworkPrinter As String
****strNetworkPrinter = GetFullNetworkPrinterName("Adobe PDF")
****If Len(strNetworkPrinter) > 0 Then ' found the network printer
********strCurrentPrinter = Application.ActivePrinter
********' change to the network printer
********Application.ActivePrinter = strNetworkPrinter
********ActiveSheet.PageSetup.PrintArea = "$A$476:$F$536"
********ActiveSheet.PrintOut** 'print something
********' change back to the previously active printer
********Application.ActivePrinter = strCurrentPrinter
********ActiveSheet.PageSetup.PrintArea = ""
****End If
End Sub
Obviously there is a function also that finds the full network printer name, I didn't think it was important, because my issue is the print area. Everything else works fine. If you need it let me know and I will post it.
I have a list of tons of items that have lost orders against them. The items themselves are catagorized in 11 different areas and I'm trying to find the top 3 of each Area. These items are sorted descending by the number of lost orders and will constantly be changing every day. Here is an example of how my workbook is set-up:
A B C D E ....
Looking for a macro that will set the print area on an accounts spreadsheet to the last non blank row, bearing in mind that all the rows contain formulas, have tried some macros but they assume the cells with formulas in them are non blank
View 4 Replies View RelatedGoal is to select range from active cell ( where coursos is ) to the top of that row. For some reason
ActiveSheet.PageSetup.PrintArea = Range("xey1":ActiveCell())
Does not work?
Making a macro that selects active cells (so anything with text in) and sets that as the print area.
Using the default builder I have:
Range("A1:AA44").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=30
Range(Selection, Selection.End(xlUp)).Select
Range("A1:AA692").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$AH$692"
[Code]...
Can trim this code with something
Range("any containing data").Select
ActiveSheet.PageSetup.PrintArea = "selection"
copying a document that was made in word into Excel. I set the print area and page size but it seems I can keep adding columns. When I check the print preview it still shows the one page but it appears wider.Will it automatically create a new page if the page size limit is reached?
View 2 Replies View RelatedI have a sheet with many merged cells.how is it possible to count the merged area and display the unique values in each area?
View 8 Replies View RelatedIn the attached file and when I click on Name or ID (B5 or B6) the input msg will appear on the same cell using the validation rule.
Actually I need any input msg to appear in the other area where i put the arrow. I tried to move the input msg to that Irea and it worked great each time i click on B5 or B6 but after closing the file and opening it later.. they will go back to the same place.
Excel 2003 used to have a feature where the user can select and set the print area. I do not see this feature in Excel 2007. So, how dow I set the print area in Excel 2007?
View 2 Replies View RelatedI can define a name to calculate the desired Print Area for a page. For example, print_area_notes: =Notes!$A$1:INDEX(Notes!$A:$Z,final_Note,5). I can then use that name when defining the Print Area via Page Setup.
The problem is it calculates the cell range immediately and substitutes that in the Print Area field. When print_area_notes changes the actual Print Area does not. Is there some formula I can enter in Print Area that will allow this value to be set dynamically? I have tried setting Print Area to =indirect("print_area_notes") but it doesn't like that. I could, but prefer not to, write a VBA function to set the Print Area but even then how do I set it to be called automatically? I'd prefer to be able to enter a formula and do it without VBA code because you then have the security issues every time the spreadsheet is opened.
I use this code to copy certain areas out of excel into a txt-file in a way that 3 Excel columns remain 3 columns in notepad. The problem with it is that it gives me a comma as the decimal separator, but I need a point.
[Code] .....
I have 2 codes that set up different "print areas". Every time i run one of the codes the print area doesn't get set unless i run the same code again. So if i run code A which will set the print area with no issues and then right afterwards i run code B then code B's print area won't get set unless i run code B a 2nd time. And if i now i run code A, the same issue happens and the print area to Code A won't get set properly unless i run the code again.
Here is part of the code that discusses the print area:
[Code] .....
I have looked for a way to set the print area of a worksheet through vb.
I would like it to find the last row of the worksheet and set it to be printed.
Some of the examples take it beyond the last row of data, because the the code that may be in the empty cells.
HTML Code: [URL] .....
I'm trying to dynamically set the print area for a given worksheet. I use the code below and it works perfectly when it's executed upon opening the tab; however, the worksheet has filters which may lengthen/shorten the required print area.
Is it possible to execute the code below when the user selects print rather than when opening the tab?
[Code] ......