I'm trying to write a formatting macro that will insert a gray divider (row) into a blank line. I'm going to do this with a keyboard command, but I'm having a hard time selecting to column AJ. Obviously this command...
...takes you to the end of the page, but I don't need to go that far, just to column AJ.
I created a VBA module (Excel 2003) that contains many generic functions that I wrote. I want to use the functions from that modules in other Excel VBA projects (something like "Include" a library of functions).
I have an Excel worksheet used as an index for my holiday photo collection. The worksheet consists of a list of geographic locations visited for a particular holiday, and a hyperlink in each of those cells to the location of the corresponding photo on my hard drive ( eg C:NZ PhotosOtago\796.jpg). When I have taken some 10,000 photos on a trip, I find this an easy way to quickly view pictures of any site visited on the holiday.
The Excel index and the photos are stored in different partitions on my computer.
I now wish to share these photos by burning them to a DVD, but I would like the Excel photo index to be also on this disk, but with hyperlinks that refer to the photo files on the DVD, not the originals on my hard drive.
If this is possible, and the format of the hyperlink that I should use in future? e.g., instead of making the hyperlink E:NZ PhotosOtago\796.jpg, could I make it ..NZ PhotosOtago\796.jpg, so that I can transfer the Excel spreadsheet to a DVD with the photos?
Also, is there a way to do a global change of the hyperlinks in all the worksheet cells?
The workbook has a tab for each day, and there are variance columns. The tabs are named for the date, like 1.20, 1.21, 1.22, etc.
For example, the formula on 1.21 in D2 is simply =C2-'1.20'!C2
That works fine, but it's a time-consuming job to always change it. You have one sheet to copy many times for all the other formulas, but then have to re-name and change that variance formula on each worksheet.
Is there a way to reference the prior worksheet without using it's specific name? =C2-'prior worksheet'!C2 would be nice...I could take my one sheet and copy it about 23 times for each workday, then copy that whole workbook to use in the subsequent months without making any formula changes.
I am looking to create a macro that will edit the code of another macro that is already setup. Basically, I have a macro that will open and close every file that has data linked to this workbook. Because of the 255 character limitation of text in Excel, I have a simple macro to open/close each so it will bring over any additional characters past 255. Now, this macro is saved to a master file, and each month we do a save as, and start a new period on it. The files we open/close are named C:/data/RCC/FY2007/10543 - ifi/10543 CurrentMonth Rec.xls
Is there a way to make a macro, that will do a simple find -- replace in the VBA code of the original sheet, so that it looks for the right files. So, with the above example, I would replace "CurrentMonth" with whatever I enter into an inputbox (say AUG for August).
And if you have a moment, is there a way to make two input boxes, one to denote the Fiscal Year (in the file path it is the entry that is FY2007) and CurrentMonth, so that if it is the new fiscal month then it will create a new directory and save in that new location? So when it is FY 2008, I would do a save as C:/data/RCC/FY2008/10543 - ifi/10543 CurrentMonth Rec.xls?
To initialize some cells/ranges, I am copying a given range and pasting it to another given range using the. Copy and .PaseSpecial methods. However, it would seem that both methods actually select the range(s) for the operations, i.e. the given ranges(s) are activated/selected thus changing the focus on the spreadsheet. I would like to perform both operations without actually selecting the given ranges.
I am receiving a 'subscript out of range' error on the lines of code below.
I would note that all variables are declared and all seem meaningful as regards what you would expect at that point.
Below is a snippet from the immediate window which indicates what the values are: completecashname C:CashDevelopmentMyFolderoutputCASH042706.xls cashsheetname Formatted Sheet cashcurrcolumn A cashfirstrow 2 cashlastrow 876
Also the workbooks are both closed at this point (but it makes no difference)
Set CashCopy = Workbooks(CompleteCashName).Sheets(CashSheetName). _ Range(CashCurrColumn & Cashfirstrow & ":K" & Cashlastrow).Value Set PelPaste = Workbooks(completepelname).Sheets(PELSheetName). _ Range((PELCurrColumn & PELlastrow)).Value
Is there a way to generically sort all pivot rows of a pivot table alphabetically each time the table is refreshed? I have a pivot table that pulls its data from a dynamic named range on a separate worksheet. As users add data to the range and refresh the pivot table the newly added items appear at the bottom of the table rather than alphabetically. I know how to specifically call out a pivot row and have it sort alphabetically (see code below) but I still want to allow users the flexibility of changing the fields and order of the pivot rows so specifically calling out a pivot row by field name isn’t going to work. I need to somehow add code that says: For each pivot row (1 to n, where n = number of pivot rows) sort each in alphabetical order.
My problem today, is actually being able to call the routines. I have tried to make a generic setup, so that each subroutine to be called is named "Macro_[number here]". So Macro_1, Macro_2 etc. By doing this I am hoping to be able to call all the subroutines using a For Each code.
After this I would like VBA to copy all the variables in a range that is bounded by startcell and endcell. Here is what my code is currently for selecting the range:
Range("A2:" & endcell).Select
Instead of A2, I would like to select startcell, but I cannot figure out how. Currently I can get VBA to recognise endcell. For example, using the formula above I can copy the range from A2 to endcell. However, when I try and use startcell as the beginning of the range (in place of A2) the code does not work.
I have two tables in two different worksheets. In the first table, rows get deleted if the value in column 'S' (the first row to the right of the table) is not '1', and columns get deleted if the value in row '2' (third row above the table) is not '1'. In the second table, the number of columns stays always the same but rows get deleted if the value in column 'N' (first column to the right of the table) is not '1'.
After the rows and columns are deleted the tables get pasted in a ppt presentation. Now the problem: I am a beginner in VBA and up til now I am only able to define a certain Range, e.g. ("A5:M17"), to be copied and pasted into ppt. But since the tables will sometimes be bigger than other times depending if rows/columns are deleted, I would rather define a dynamic Range. The top left cell of both tabels to be copied is always 'A5' but the bottom right cell changes.
I have a spreadsheet with Employee details and each employee has a Indicator and a Score. I'm battling to get Excel to do a lookup on the Indicator, find the applicable Score Range, see if the individual's Score falls within the range and then inserts the relevant % into a cell in a specific row of each employee.
If we did not have more than 2000 doing it manualy might have been an option. Also, the Percentages and Score Ranges in the matrix might change a couple of times....
I am having a few problems selecting a range. What I am trying to do is select a range where the number of columns stay the same, but the rows are variable; ranging from one row upwards. At the end of the rows, there is a blank row. I currently have;
I am trying to select a range of cells. The range I need to select starts in Cell A1. I need to select every row with data in column 1 up to the first row with no data. I then need to expand the range to all columns up the last column with data, including columns with blank cells.
The problem with my code relates to the selection of columns. It is only selecting up to column F. Column G is empty, Column H has data, Columns I-J are empty, Columns K-R have data.
I'm trying to obtain a formula that allows me to select the last 20 numbers in a range of numbers that continue to increase weekly. i.e column a3-a3000 increases by one number every week. I need the formula to sum the lowest 10 numbers of the last 20. If there is 30 numbers from a13-a33 week one. I require the formula to pick up the 10 lowest from the last 20 (a13-a33). Week two the cell increases by on to a34. The formula would then have to pick up the lowest 10 from the last 20 (a14-a44) and so on.
When I use this code is only gets A1:N6...: is there anyway to get it to select the range all the way to the end of the data. I tried CurrentRegion and some other stuff but can't get it to work.: The range may go upto line 500 or 3 I just never know.
I have a spreadsheet where I need to perform a calculation on the lowest 10 of the 20 most recent entries. Currently I am just deleting the oldest entry and putting in the new data but I now desire to keep all my data.
How do I select the new range of 20 rows and then tell it to take the 10 lowest values to perform the calculation
I have 2 worksheets where I need to transfer a certain amount of data from one to another depending on dates.
Sheet ‘A’, is the main data source. Col A holds all 365 dates from 1 Jan to 31 Dec(starting at A2), and the subsequent data for each day is held in cols B to P. Sheet ‘B’ is the destination where the user will specify a key date in cell A3, then in A4 to A32, formulae populate the next 29 dates (ie so there is a continuous run of 30 days).
What I need to do, is to identify the same 30 day range from sheet B on sheet A, then to copy the data in Cols B to P for that range back onto sheet B (pasting in at D3)
Question 1) Is there a way that whenever I select Cell B39 that it will copy cells B39:V39 ? From there I can manually paste that selection to where I need to.
If this is possible, it would save the effort of having to select B39:V39 manually and clicking CTRL-C. I am gathering data and the copying and pasting is killing me.
Question 2) Is it possible that when I click on Cell A9 that it can paste any data that I just copied from another worksheet to cells A9:D29? The size of the selection that I would copy is the same size as A9:D29 (4 columns & 29 rows).
I have searched for a solution, but I am not having much luck since I am anaware of the proper terminology for these types of actions.