"ML0940" wrote in message
...
Hi,
For the first part of your question, this may help.
I have a budget spread sheet and this is how I handle it:

I am using a named range, but you can also use a hardcoded range as well;
then I use the offset to copy what I need, then I use paste special
(PasteValues)
If you use PasteValues, then the values will be pasted indentically to the
copied values

'Copy Column C bills
Sheets(1).Activate
Sh1bW.Offset(0, 2).Copy
'Paste bills one column to the right of named range Sh2bw
Sheets(2).Activate
Sh2bw.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone

For the clear part, it is your source range .ClearContents

Also, I actually have a macro callled Shift Left, see if this helps you as
well
Between the two, you should be able to piece something together.

Thanks, this is the sort of thing I was looking for. Does this work,
especially the Offset, if the range is discontinuous? Given that I want to
ignore rows that contain subtotalling formulas, I thought I might use
SpecialCells to get a range containing only those cells that have constant
values -- but that would be a discontinuous range.

By the way, I noticed this in your ShiftLeft macro code:

Range("B" & i).Value = Range("B" & i).Value - Range("C" & i).Value
'Change cell format in Column B back to accounting
Range("B" & i).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($*
""-""??_);_(@_)"

In testing my own looping code, I found that setting a cell's Value property
seemed to reset its format, and so I used code to save the format and then
restore the format after setting the value. I wasn't sure that I ought to
have to do that, and thought maybe I was missing something. But your code
suggests that losing the formatting is just something that happens, and you
have to deal with it. Is that right?

Thanks for your assistance,
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)