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

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
>
>
>