On 2002-03-26 12:30, Barrie Davidson wrote:
I'm not totally sure of your requirements. If I understand correctly, the following code will take the values in column B and copy them to first blank column from column B (leaving the original formulas in column B).<pre>
No it is just the other way: take the formules in column B and copy them to column C (leaving the original values in column B)
The following day : take the formules in column C and copy them to column D (leaving the original values in column C) and so on each day a further column.
Thats what I mean.
Range("B1:" & Range("B65536").End(xlUp).Address).Copy
Range(Range("IV1").End(xlToLeft).Offset(0, 1) _
.Address).PasteSpecial _
Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False</pre>
Is this what you need?
_________________
Barrie Davidson
My Excel Web Page
This message was edited by Barrie Davidson on 2002-03-26 12:31
Range("IV1").End(xlToLeft).Offset(0, -1).EntireColumn.Insert
Range(Range("IV1").End(xlToLeft).Address, _
Range("IV1").End(xlToLeft).End(xlDown). _
Address).Copy
Range("IV1").End(xlToLeft).Offset(0, -1).PasteSpecial _
Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
What do you mean with: Range("IV1") ?On 2002-03-26 12:59, Barrie Davidson wrote:
Okay, how about this then:
Code:Range("IV1").End(xlToLeft).Offset(0, -1).EntireColumn.Insert Range(Range("IV1").End(xlToLeft).Address, _ Range("IV1").End(xlToLeft).End(xlDown). _ Address).Copy Range("IV1").End(xlToLeft).Offset(0, -1).PasteSpecial _ Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False
Are we there yet?
On 2002-03-26 14:43, verluc wrote:
What do you mean with: Range("IV1") ?On 2002-03-26 12:59, Barrie Davidson wrote:
Okay, how about this then:
Code:Range("IV1").End(xlToLeft).Offset(0, -1).EntireColumn.Insert Range(Range("IV1").End(xlToLeft).Address, _ Range("IV1").End(xlToLeft).End(xlDown). _ Address).Copy Range("IV1").End(xlToLeft).Offset(0, -1).PasteSpecial _ Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False
Are we there yet?
Thanks.
Sorry,but this macro dont work.I receive an error on the line Range("IV1")On 2002-03-26 15:09, Barrie Davidson wrote:
On 2002-03-26 14:43, verluc wrote:
What do you mean with: Range("IV1") ?On 2002-03-26 12:59, Barrie Davidson wrote:
Okay, how about this then:
Code:Range("IV1").End(xlToLeft).Offset(0, -1).EntireColumn.Insert Range(Range("IV1").End(xlToLeft).Address, _ Range("IV1").End(xlToLeft).End(xlDown). _ Address).Copy Range("IV1").End(xlToLeft).Offset(0, -1).PasteSpecial _ Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False
Are we there yet?
Thanks.
Range("IV1") is the last cell in row 1. I use that to get the last column to the right (I wasn't sure if you had anything in cell A1, so I assumed you did not).
Regards,
Sorry,but this macro dont work.I receive an error on the line Range("IV1")
In column A I have the symbols of the stocks.In column B are the quotes of the stocks.
Thanks for your answer.
Here is the complete macro,who is in my program:Sub Copy()On 2002-03-26 15:29, Barrie Davidson wrote:
Sorry,but this macro dont work.I receive an error on the line Range("IV1")
In column A I have the symbols of the stocks.In column B are the quotes of the stocks.
Thanks for your answer.
I'm curious, which line gave the error (three lines begin with Range("IV1"))? It worked fine for me when I tested it.
Here is the complete macro,who is in my program:Sub Copy()
Sheets("Portefeuille").Select
Range("IV1").End(xlToLeft).Offset(0, -1).EntireColumn.Insert
Range(Range("IV1").End(xlToLeft).Address, _
Range("IV1").End(xlToLeft).End(xlDown). _
Address).Copy
Range("IV1").End(xlToLeft).Offset(0, -1).PasteSpecial _
Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
End Sub
It is on the first Range("IV1") that I receive the error.
Many thanks
On 2002-03-26 15:43, Barrie Davidson wrote:
Here is the complete macro,who is in my program:Sub Copy()
Sheets("Portefeuille").Select
Range("IV1").End(xlToLeft).Offset(0, -1).EntireColumn.Insert
Range(Range("IV1").End(xlToLeft).Address, _
Range("IV1").End(xlToLeft).End(xlDown). _
Address).Copy
Range("IV1").End(xlToLeft).Offset(0, -1).PasteSpecial _
Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
End Sub
It is on the first Range("IV1") that I receive the error.
Many thanks
Do you have anything in cell B1? I'm betting not; if you only have something in cell A1, the macro is trying to go to the left of cell A1.
Range("IV1").End(xlToLeft).Offset(0, -1)
This code will go to the column left of the rightmost column of data in row 1. For example, if your rightmost cell is C1, it will go to cell B1. As you can see, you will get an error if your rightmost cell (in row 1) is A1.
Am I correct?
Great, It works.Many,Many thanks.
On 2002-03-26 15:52, verluc wrote:
On 2002-03-26 15:43, Barrie Davidson wrote:
Here is the complete macro,who is in my program:Sub Copy()
Sheets("Portefeuille").Select
Range("IV1").End(xlToLeft).Offset(0, -1).EntireColumn.Insert
Range(Range("IV1").End(xlToLeft).Address, _
Range("IV1").End(xlToLeft).End(xlDown). _
Address).Copy
Range("IV1").End(xlToLeft).Offset(0, -1).PasteSpecial _
Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
End Sub
It is on the first Range("IV1") that I receive the error.
Many thanks
Do you have anything in cell B1? I'm betting not; if you only have something in cell A1, the macro is trying to go to the left of cell A1.
Range("IV1").End(xlToLeft).Offset(0, -1)
This code will go to the column left of the rightmost column of data in row 1. For example, if your rightmost cell is C1, it will go to cell B1. As you can see, you will get an error if your rightmost cell (in row 1) is A1.
Am I correct?
Great, It works.Many,Many thanks.