A different way to Copy Paste Value

[Mizzness]

All.

I have formulas in cells A1:A3.
I would like to have my macro:
1. Copy the formula from that range (A1:A3) to B1:B3.
2. It will then go back to A1:A3 & copy/paste value that range.
This will happen daily so the range will move to the right with each passing day.
I've used the recorder but then I would have to change the range in the code manually.

Any & all guidance is appreciated.

 

[AnotherHiggins]

-> This will happen daily so the range will move to the right with each passing day

This sets off flags right away. Up until Excel 2007, there are only 256 columns available in a spreadsheet. If this is updated daily, that is only about 9 months of data that you can fit in your spreadsheet.

Even if you are using 2007, I'd suggest that you have days go down the sheet rather than across.

Other than that, how about posting the code you have so far....

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

I agree with John!

It's hardly ever a good idea to store data horizontally, as you have described.

However to REPORT and summarize data, is a different question. But still, the SOURCE of such a report, ought to be from normalized data.

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[Mizzness]

Gentlemen,

Thanks for the suggestion about moving data horizontally (which I agree with).
However, this is not an issue as the file is saved on a monthly basis & only business days are used.

The code below would have to be changed daily to accomodate the new range. For tomorrow I would have to change column "M" to "N" & "N" to "O".
How can I get the code to move over one column daily as explained earlier ?

Range("M11:M13").Select
Selection.Copy
Range("N11").Select
ActiveSheet.Paste
Range("M11:M13").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Calculate
End Sub


Thanx for any & all guidance.

 

[SkipVought]

So this is a monthly report?

You are storing the daily data in a table?

Why not use the PivotTable Wizard to build your report as often as you like?

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[mscallisto]

Can you store the values in two never used cells
e.g. Z255 = A and Z256 = B

then code Range("whats in z255"&"11:"& "whats in z256"&"13")

then at process end, increase z255 and 256 by 1 letter for tomorrows run.

 

[Mizzness]

Skip,

Can't go the pivot table route as the formula is fed off of the number of accrual days. By changing the days the formulas will only give you data for one day. This is why I would like to move the formulas from one day to the next & hard code the previous day.

 

[TopJack]

Mizzness,

I guess you are looking for code similar to this :-

Sub paste_next_day()
Dim first_row As Integer, last_row As Integer, columns_used As Integer
Dim ws As Worksheet

first_row = 11: last_row = 13 [COLOR=green]'declare rows to be copied[/color]
Set ws = ActiveSheet    [COLOR=green]'declare current sheet[/color]
columns_used = ws.UsedRange.Columns.Count  [COLOR=green]'calculate last column used on sheet, ie last day updated[/color]

ws.Range(Cells(first_row, columns_used), Cells(last_row, columns_used)).Copy   [COLOR=green]'define last day range to copy from[/color]

ws.Paste (Cells(first_row, columns_used + 1))  [COLOR=green]'paste formulas over to a new day[/color]

ws.Cells(first_row, columns_used).PasteSpecial Paste:=xlPasteValues    [COLOR=green]'overwrite last days formulas with values[/color]

Application.CutCopyMode = False [COLOR=green]'turn off copy mode[/color]

ws.Cells(first_row, columns_used + 1).Select   [COLOR=green]'send cursor to new day home position[/color]

Calculate   [COLOR=green]'force a calculation on sheet[/color]

End Sub

There is many ways to achieve your results but thats one of them. A word of caution using "UsedRange", depending on how you 'use' your sheet its possible this may return the wrong column. If you experience this issue you can use this line instead that may be more reliable.

columns_used = ws.Cells.Find(What:="*", SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column

Enjoy,
TopJack