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, "Hammer...@gmail.com" <Hammer...@gmail.com> 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.

"(E-Mail Removed)" wrote:
>
> On Jun 29, 3:14 pm, "Hammer...@gmail.com" <Hammer...@gmail.com> 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 <peter...@verizonXSPAM.net> 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.
>
> "Hammer...@gmail.com" wrote:
>
> > On Jun 29, 3:14 pm, "Hammer...@gmail.com" <Hammer...@gmail.com> 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

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.

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.


"(E-Mail Removed)" 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 <peter...@verizonXSPAM.net> 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.
> >
> > "Hammer...@gmail.com" wrote:
> >
> > > On Jun 29, 3:14 pm, "Hammer...@gmail.com" <Hammer...@gmail.com> 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

Still not working here, get exactly the same result...

I truly dont understand this.

Why only a couple cells are affected??

On Jul 12, 3:27*pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> 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.
>
>
>
>
>
> "Hammer...@gmail.com" 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 <peter...@verizonXSPAM.net> 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.
>
> > > "Hammer...@gmail.com" wrote:
>
> > > > On Jun 29, 3:14 pm, "Hammer...@gmail.com" <Hammer...@gmail.com> 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

What result do you get? And what problem is happening?

What do you mean by only a couple of cells are affected?



"(E-Mail Removed)" wrote:
>
> Still not working here, get exactly the same result...
>
> I truly dont understand this.
>
> Why only a couple cells are affected??
>
> On Jul 12, 3:27 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > 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.
> >
> >
> >
> >
> >
> > "Hammer...@gmail.com" 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 <peter...@verizonXSPAM.net> 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.
> >
> > > > "Hammer...@gmail.com" wrote:
> >
> > > > > On Jun 29, 3:14 pm, "Hammer...@gmail.com" <Hammer...@gmail.com> 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

--

Dave Peterson

Heres an example:

RANGE M10:M15

23/06/2008
30/06/2008
12/05/2008
07/07/2008
Never
05/05/2008

based on format is dd/mm/yyyy

When using Sheets("Settings").Range(TempWeekRange + "3" + ":" +
TempWeekRange +
"8").Value = _
Sheets("Settings").Range(TempWeekRange + "10" + ":" + TempWeekRange
+ "15").Value or any other transfer other than copy and paste the
result is this on range M3:M8

6/23/2008
6/30/2008
05/12/2008
07/07/2008
Never
05/05/2008

it changes the format to mm/dd/yyyy which messes up calculations
afterwards.
The weird thing is that the first two rows I cannot change the format
of it, it is locked.

I dont understand this one.








On Jul 12, 9:38*pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> What result do you get? *And what problem is happening?
>
> What do you mean by only a couple of cells are affected?
>
>
>
>
>
> "Hammer...@gmail.com" wrote:
>
> > Still not working here, get exactly the same result...
>
> > I truly dont understand this.
>
> > Why only a couple cells are affected??
>
> > On Jul 12, 3:27 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > 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.
>
> > > "Hammer...@gmail.com" 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 <peter...@verizonXSPAM.net> 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.
>
> > > > > "Hammer...@gmail.com" wrote:
>
> > > > > > On Jun 29, 3:14 pm, "Hammer...@gmail.com" <Hammer...@gmail.com>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
>
> --
>
> Dave Peterson