Hi,

I have this code :

Sheets("Settings").Range(TempWeekRange + "3" + ":" + TempWeekRange +
"8").Value = _
Sheets("Settings").Range(TempWeekRange + "10" + ":" + TempWeekRange
+ "15").Value

TempWeekRange is a string variable that will point to a column.

IE : M10:M15
23/06/2008
09/06/2008
12/05/2008
16/06/2008
Never
05/05/2008

I have set up the format for this range "dd/mm/yyyy"
The problem is that the code above copies the data to the range M3:M8
as :
6/23/2008
06/09/2008
05/12/2008
6/16/2008
Never
05/05/2008

The format is all wrong, despite having it formatted the same "dd/mm/
yyyy"
I've tried Sheets("Settings").Range(TempWeekRange + "3" + ":" +
TempWeekRange + "8").NumberFormat = "dd/mm/yyyy" before and after and
it doesnt work.

Besides splitting each row into :

Sheets("Settings").Range(TempWeekRange + "3" + ":" + TempWeekRange +
"8").Value = _
FORMAT(Sheets("Settings").Range(TempWeekRange + "10" + ":" +
TempWeekRange + "15").Value,"dd/mmm/yyyy")

Is there an easier way, to keep it a single line?

Sorry I meant:

Sheets("Settings").Range(TempWeekRange + "3").Value = _
FORMAT(Sheets("Settings").Range(TempWeekRange + "10").Value,"dd/mmm/
yyyy")

On Jun 29, 3:14*pm, " wrote:
Sorry I meant:

*Sheets("Settings").Range(TempWeekRange + "3").Value = _
*FORMAT(Sheets("Settings").Range(TempWeekRange + "10").Value,"dd/mmm/
yyyy")

Why cant I copy between the range and keep the date format without
excel messing it up?

You can.

But this isn't doing any copy or paste.

Record a macro when you do it manually and you'll have the code you need.

" wrote:

On Jun 29, 3:14 pm, " wrote:
Sorry I meant:

Sheets("Settings").Range(TempWeekRange + "3").Value = _
FORMAT(Sheets("Settings").Range(TempWeekRange + "10").Value,"dd/mmm/
yyyy")

Why cant I copy between the range and keep the date format without
excel messing it up?

--

Dave Peterson

Hi,

I know about the copy and paste, but why

Sheets("Settings").Range(TempWeekRange + "3" + ":" + TempWeekRange +
"8").Value = Sheets("Settings").Range(TempWeekRange + "10" + ":" +
TempWeekRange + "15").Value

doesnt work when both ranges have the same format??


On Jul 12, 1:47*pm, Dave Peterson wrote:
You can.

But this isn't doing any copy or paste.

Record a macro when you do it manually and you'll have the code you need.

" wrote:

On Jun 29, 3:14 pm, " wrote:
Sorry I meant:

*Sheets("Settings").Range(TempWeekRange + "3").Value = _
*FORMAT(Sheets("Settings").Range(TempWeekRange + "10").Value,"dd/mmm/
yyyy")

Why cant I copy between the range and keep the date format without
excel messing it up?

--

Dave Peterson

This is just assigning the values. It doesn't touch the format.

Since you don't want to do copy|paste in your code, you could try this:

Dim SourceRng As Range
Dim DestCell As Range

With Sheets("Settings")
Set SourceRng = .Cells(10, TempWeekRange).Resize(6, 1)
Set DestCell = .Cells(3, TempWeekRange)
End With

With SourceRng
DestCell.Resize(.Rows.Count, .Columns.Count).NumberFormat = .NumberFormat
DestCell.Resize(.Rows.Count, .Columns.Count).Value = .Value
End With

==========
But I think you're in for a disappointment. Most macros that do anything will
kill the clipboard. This did for me.


" wrote:

Hi,

I know about the copy and paste, but why

Sheets("Settings").Range(TempWeekRange + "3" + ":" + TempWeekRange +
"8").Value = Sheets("Settings").Range(TempWeekRange + "10" + ":" +
TempWeekRange + "15").Value

doesnt work when both ranges have the same format??

On Jul 12, 1:47 pm, Dave Peterson wrote:
You can.

But this isn't doing any copy or paste.

Record a macro when you do it manually and you'll have the code you need.

" wrote:

On Jun 29, 3:14 pm, " wrote:
Sorry I meant:

Sheets("Settings").Range(TempWeekRange + "3").Value = _
FORMAT(Sheets("Settings").Range(TempWeekRange + "10").Value,"dd/mmm/
yyyy")

Why cant I copy between the range and keep the date format without
excel messing it up?

--

Dave Peterson

--

Dave Peterson

I forgot to mention that the reason I am not doing a copy/paste is
because I dont want to have empty the clipboard because of this.