Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to automate
Each month a program adds a large amount of fresh data relating to the
previous month onto a spreadsheet called "data" which is hiden. On another "Summary" sheet the fresh months data needs to be added and summariesd. At the moment, each month I have to go to the Summary sheet, in order to add another months data I first cut the summary from the end of the last column and move it one column to the right. Then highlight the last months figures and drag over to the now empty column (previously the Sumarised column) which drags across the formulas to populate this months figures. Then I have to go into the final summary colmn (which I have just cut and pasted one column to the right) and adjust the formulas which now stop one column short. How is the best way to achieve the above, I tried creating a macro, but I had a number of issues, mainly with trying to sellect the last column and then last but 2 column. I did wonder if I could hide say 24 columns (after draging the formulas across them all first) then have the Summary, so each month I would have to unhide one column.. but I would like to automate it and do not know where to start. Any help most gratefully accepted. Bob |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to automate
Bob;
One lesson I learned early on (by trial and error - mostly error) was to use named ranges as much as possible when you need to automate tasks. Named ranges allow your code to refer to the same "place" in a spreadsheet even if you insert or delete rows/columns or move things around. I have a set of spreadsheets that need to do pretty much what you describe - look up new data every month and create a monthly summary, but also add certain totals from that summary to a long list of prior months' data so it can be graphed (I have these in columns on a separate sheet, a new column for each month). What I did was give a name (CurrentMonth) to the range - can be any cell(s) in the column or even the entire column - that has the latest month's data in it. For the new month, I insert a column to the left of this, then copy (as values) from CurrentMonth to the new column. Then I update the data and the CurrentMonth column (which contains my formulas for the totals) updates to reflect the latest data. Since I never moved the formulas to new cells, I don't have to adjust them at all (but even here I recommend using named ranges as much as possible in your formulas - it makes it so much easier to edit your spreadsheet since you don't have to figure out changes in cell references, or even worry about relative/absolute referencing). With VBA and the named range, it is pretty easy to do this: With Range("CurrentMonth") .EntireColumn.Insert .Copy .Offset(0, -1).PasteSpecial xlPasteValues End With Application.CutCopyMode = False "Robert Gillard" wrote: Each month a program adds a large amount of fresh data relating to the previous month onto a spreadsheet called "data" which is hiden. On another "Summary" sheet the fresh months data needs to be added and summariesd. At the moment, each month I have to go to the Summary sheet, in order to add another months data I first cut the summary from the end of the last column and move it one column to the right. Then highlight the last months figures and drag over to the now empty column (previously the Sumarised column) which drags across the formulas to populate this months figures. Then I have to go into the final summary colmn (which I have just cut and pasted one column to the right) and adjust the formulas which now stop one column short. How is the best way to achieve the above, I tried creating a macro, but I had a number of issues, mainly with trying to sellect the last column and then last but 2 column. I did wonder if I could hide say 24 columns (after draging the formulas across them all first) then have the Summary, so each month I would have to unhide one column.. but I would like to automate it and do not know where to start. Any help most gratefully accepted. Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automate MTD summation | Excel Worksheet Functions | |||
Automate a Graph? | Charts and Charting in Excel | |||
Automate without add-ins | Excel Discussion (Misc queries) | |||
Automate Add-in | Excel Discussion (Misc queries) | |||
Automate Add-In | Excel Programming |