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

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.


" 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

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 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.





" 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

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

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



" 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 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.





" 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

--

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 wrote:
What result do you get? *And what problem is happening?

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





" 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 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.

" 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

--

Dave Peterson

Another problem for the Copy and Paste solution is that Sheet Settings
is a hidden sheet and is never the Activesheet.

I don't see where you tried my suggestion--or how it failed.


" wrote:

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 wrote:
What result do you get? And what problem is happening?

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





" 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 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.

" 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

--

Dave Peterson

--

Dave Peterson

Sheets don't have to be visible or even active to copy or paste.

" wrote:

Another problem for the Copy and Paste solution is that Sheet Settings
is a hidden sheet and is never the Activesheet.

--

Dave Peterson

Dave I tried your example and it didnt work, same result.

Another tidbit is that if I use:

ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "3").Value =
Format(Sheets("Settings").Range(TempWeekRange + "10").Value, "dd/mmm/
yyyy")
ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "4").Value =
Format(Sheets("Settings").Range(TempWeekRange + "11").Value, "dd/mmm/
yyyy")
ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "5").Value =
Format(Sheets("Settings").Range(TempWeekRange + "12").Value, "dd/mmm/
yyyy")
ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "6").Value =
Format(Sheets("Settings").Range(TempWeekRange + "13").Value, "dd/mmm/
yyyy")
ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "7").Value =
Format(Sheets("Settings").Range(TempWeekRange + "14").Value, "dd/mmm/
yyyy")
ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "8").Value =
Format(Sheets("Settings").Range(TempWeekRange + "15").Value, "dd/mmm/
yyyy")

then it copies it properly with correct format.
The problem with it is that if there are other workbooks opened then
this is slow as mollasses and literally takes seconds to complete.
If it it the only workbook opened then it is fast.
Thats why I dont want to use this approach.


On Jul 13, 8:50*am, Dave Peterson wrote:
Sheets don't have to be visible or even active to copy or paste.

" wrote:

Another problem for the Copy and Paste solution is that Sheet Settings
is a hidden sheet and is never the Activesheet.

--

Dave Peterson

Dave here is the code to help you figure it out.

I have 7 sheets named after the weekdays and one called "Month"
On the daily sheets i call this sub to update this list only if the
weekday is prior to todays date.
Also on each daily sheet it just referencies it to the Settings sheet
and are also formated as "ddmmyyyy", but that is no the problem.


Public Sub UpdateLastSalesDate(DailyWeek, SalesDateRange As Integer)
Dim StartWeek, EndWeek As Date
Dim TempWeekSheet, TempWeekRange As String
Dim Sales, NewWeekFlag As Integer
Application.ScreenUpdating = True
'dailyweek<8 means its called by one of the daily sheets
' and thats where I am having the trouble
If DailyWeek < 8 Then
'Get sheet name
TempWeekSheet = WeekdayName(DailyWeek)
'get the sales
Sales = ThisWorkbook.Sheets(TempWeekSheet).Range("N5").Val ue
'Used to get the column range starting with M
TempWeekRange = Chr(75 + DailyWeek)
If Sales 5 Then Sales = 5
'ThisWorkbook.Sheets("Settings").Range(TempWeekRan ge + "3").Value =
Format(Sheets("Settings").Range(TempWeekRange + "10").Value, "dd/mmm/
yyyy")
'ThisWorkbook.Sheets("Settings").Range(TempWeekRan ge + "4").Value =
Format(Sheets("Settings").Range(TempWeekRange + "11").Value, "dd/mmm/
yyyy")
'ThisWorkbook.Sheets("Settings").Range(TempWeekRan ge + "5").Value =
Format(Sheets("Settings").Range(TempWeekRange + "12").Value, "dd/mmm/
yyyy")
'ThisWorkbook.Sheets("Settings").Range(TempWeekRan ge + "6").Value =
Format(Sheets("Settings").Range(TempWeekRange + "13").Value, "dd/mmm/
yyyy")
'ThisWorkbook.Sheets("Settings").Range(TempWeekRan ge + "7").Value =
Format(Sheets("Settings").Range(TempWeekRange + "14").Value, "dd/mmm/
yyyy")
'ThisWorkbook.Sheets("Settings").Range(TempWeekRan ge + "8").Value =
Format(Sheets("Settings").Range(TempWeekRange + "15").Value, "dd/mmm/
yyyy")

ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "3" + ":" +
TempWeekRange + "8").NumberFormat = _
ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "10" + ":" +
TempWeekRange + "15").NumberFormat
ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "3" + ":" +
TempWeekRange + "8").Value = _
ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "10" + ":" +
TempWeekRange + "15").Value
'update list only if it is a prior weekday not today
If DailyWeek < Weekday(Date) Then
'check number of boxes, if no boxes were available then dont update
sales#
If ThisWorkbook.Sheets(TempWeekSheet).Range("N9").Val ue 0 Then
'ThisWorkbook.Sheets("Settings").Range(TempWeekRan ge + CStr(3 +
Sales)).Value = Format(Sheets("Settings").Range("E24").Value +
DailyWeek - 2, "dd/mmm/yyyy")
ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + CStr(3 +
Sales)).Value = CDate(Sheets("Settings").Range("E24").Value +
DailyWeek - 2)
End If
End If

Else
'Called by Monthly sheet or by end of the week sub
StartWeek = ThisWorkbook.Sheets("Settings").Range("E24").Value
EndWeek = StartWeek + SalesDateRange
If EndWeek StartWeek + 7 Then EndWeek = StartWeek + 5
'flag for new week or daily check from monthlysheet
If SalesDateRange = 8 Then
NewWeekFlag = 10
Else
NewWeekFlag = 3
End If

Do While StartWeek < EndWeek
TempWeekRange = Chr(75 + Weekday(StartWeek))
TempWeekSheet = WeekdayName(Weekday(StartWeek))
Sales = ThisWorkbook.Sheets(TempWeekSheet).Range("N5").Val ue
If Sales 5 Then Sales = 5
If ThisWorkbook.Sheets(TempWeekSheet).Range("N9").Val ue 0 Then
ThisWorkbook.Sheets("Settings").Range(TempWeekRang e +
CStr(NewWeekFlag + Sales)).Value = Format(StartWeek, "dd/mmm/yyyy")
End If
StartWeek = StartWeek + 1
Loop
If SalesDateRange = 8 Then
'funnily or not this one works.
ThisWorkbook.Sheets("Settings").Range("M3:R8").Num berFormat = _
ThisWorkbook.Sheets("Settings").Range("M10:R15").N umberFormat
ThisWorkbook.Sheets("Settings").Range("M3:R8").Val ue = _
ThisWorkbook.Sheets("Settings").Range("M10:R15").V alue
End If
End If
Application.ScreenUpdating = False
End Sub

My suggestion worked in my testing.

I'm surprised that your code copied the formatting. I would have guessed excel
saw you were entering dates and just used its short date format--not the format
from the "sending" cells.

" wrote:

Dave I tried your example and it didnt work, same result.

Another tidbit is that if I use:

ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "3").Value =
Format(Sheets("Settings").Range(TempWeekRange + "10").Value, "dd/mmm/
yyyy")
ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "4").Value =
Format(Sheets("Settings").Range(TempWeekRange + "11").Value, "dd/mmm/
yyyy")
ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "5").Value =
Format(Sheets("Settings").Range(TempWeekRange + "12").Value, "dd/mmm/
yyyy")
ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "6").Value =
Format(Sheets("Settings").Range(TempWeekRange + "13").Value, "dd/mmm/
yyyy")
ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "7").Value =
Format(Sheets("Settings").Range(TempWeekRange + "14").Value, "dd/mmm/
yyyy")
ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "8").Value =
Format(Sheets("Settings").Range(TempWeekRange + "15").Value, "dd/mmm/
yyyy")

then it copies it properly with correct format.
The problem with it is that if there are other workbooks opened then
this is slow as mollasses and literally takes seconds to complete.
If it it the only workbook opened then it is fast.
Thats why I dont want to use this approach.

On Jul 13, 8:50 am, Dave Peterson wrote:
Sheets don't have to be visible or even active to copy or paste.

" wrote:

Another problem for the Copy and Paste solution is that Sheet Settings
is a hidden sheet and is never the Activesheet.

--

Dave Peterson

--

Dave Peterson

I'd still try the copy|paste or assigning both the .numberformat and .value.

" wrote:

Dave here is the code to help you figure it out.

I have 7 sheets named after the weekdays and one called "Month"
On the daily sheets i call this sub to update this list only if the
weekday is prior to todays date.
Also on each daily sheet it just referencies it to the Settings sheet
and are also formated as "ddmmyyyy", but that is no the problem.

Public Sub UpdateLastSalesDate(DailyWeek, SalesDateRange As Integer)
Dim StartWeek, EndWeek As Date
Dim TempWeekSheet, TempWeekRange As String
Dim Sales, NewWeekFlag As Integer
Application.ScreenUpdating = True
'dailyweek<8 means its called by one of the daily sheets
' and thats where I am having the trouble
If DailyWeek < 8 Then
'Get sheet name
TempWeekSheet = WeekdayName(DailyWeek)
'get the sales
Sales = ThisWorkbook.Sheets(TempWeekSheet).Range("N5").Val ue
'Used to get the column range starting with M
TempWeekRange = Chr(75 + DailyWeek)
If Sales 5 Then Sales = 5
'ThisWorkbook.Sheets("Settings").Range(TempWeekRan ge + "3").Value =
Format(Sheets("Settings").Range(TempWeekRange + "10").Value, "dd/mmm/
yyyy")
'ThisWorkbook.Sheets("Settings").Range(TempWeekRan ge + "4").Value =
Format(Sheets("Settings").Range(TempWeekRange + "11").Value, "dd/mmm/
yyyy")
'ThisWorkbook.Sheets("Settings").Range(TempWeekRan ge + "5").Value =
Format(Sheets("Settings").Range(TempWeekRange + "12").Value, "dd/mmm/
yyyy")
'ThisWorkbook.Sheets("Settings").Range(TempWeekRan ge + "6").Value =
Format(Sheets("Settings").Range(TempWeekRange + "13").Value, "dd/mmm/
yyyy")
'ThisWorkbook.Sheets("Settings").Range(TempWeekRan ge + "7").Value =
Format(Sheets("Settings").Range(TempWeekRange + "14").Value, "dd/mmm/
yyyy")
'ThisWorkbook.Sheets("Settings").Range(TempWeekRan ge + "8").Value =
Format(Sheets("Settings").Range(TempWeekRange + "15").Value, "dd/mmm/
yyyy")

ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "3" + ":" +
TempWeekRange + "8").NumberFormat = _
ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "10" + ":" +
TempWeekRange + "15").NumberFormat
ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "3" + ":" +
TempWeekRange + "8").Value = _
ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "10" + ":" +
TempWeekRange + "15").Value
'update list only if it is a prior weekday not today
If DailyWeek < Weekday(Date) Then
'check number of boxes, if no boxes were available then dont update
sales#
If ThisWorkbook.Sheets(TempWeekSheet).Range("N9").Val ue 0 Then
'ThisWorkbook.Sheets("Settings").Range(TempWeekRan ge + CStr(3 +
Sales)).Value = Format(Sheets("Settings").Range("E24").Value +
DailyWeek - 2, "dd/mmm/yyyy")
ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + CStr(3 +
Sales)).Value = CDate(Sheets("Settings").Range("E24").Value +
DailyWeek - 2)
End If
End If

Else
'Called by Monthly sheet or by end of the week sub
StartWeek = ThisWorkbook.Sheets("Settings").Range("E24").Value
EndWeek = StartWeek + SalesDateRange
If EndWeek StartWeek + 7 Then EndWeek = StartWeek + 5
'flag for new week or daily check from monthlysheet
If SalesDateRange = 8 Then
NewWeekFlag = 10
Else
NewWeekFlag = 3
End If

Do While StartWeek < EndWeek
TempWeekRange = Chr(75 + Weekday(StartWeek))
TempWeekSheet = WeekdayName(Weekday(StartWeek))
Sales = ThisWorkbook.Sheets(TempWeekSheet).Range("N5").Val ue
If Sales 5 Then Sales = 5
If ThisWorkbook.Sheets(TempWeekSheet).Range("N9").Val ue 0 Then
ThisWorkbook.Sheets("Settings").Range(TempWeekRang e +
CStr(NewWeekFlag + Sales)).Value = Format(StartWeek, "dd/mmm/yyyy")
End If
StartWeek = StartWeek + 1
Loop
If SalesDateRange = 8 Then
'funnily or not this one works.
ThisWorkbook.Sheets("Settings").Range("M3:R8").Num berFormat = _
ThisWorkbook.Sheets("Settings").Range("M10:R15").N umberFormat
ThisWorkbook.Sheets("Settings").Range("M3:R8").Val ue = _
ThisWorkbook.Sheets("Settings").Range("M10:R15").V alue
End If
End If
Application.ScreenUpdating = False
End Sub

--

Dave Peterson

I see you tried that.

I don't have another suggestion.

Dave Peterson wrote:

I'd still try the copy|paste or assigning both the .numberformat and .value.

" wrote:

Dave here is the code to help you figure it out.

I have 7 sheets named after the weekdays and one called "Month"
On the daily sheets i call this sub to update this list only if the
weekday is prior to todays date.
Also on each daily sheet it just referencies it to the Settings sheet
and are also formated as "ddmmyyyy", but that is no the problem.

Public Sub UpdateLastSalesDate(DailyWeek, SalesDateRange As Integer)
Dim StartWeek, EndWeek As Date
Dim TempWeekSheet, TempWeekRange As String
Dim Sales, NewWeekFlag As Integer
Application.ScreenUpdating = True
'dailyweek<8 means its called by one of the daily sheets
' and thats where I am having the trouble
If DailyWeek < 8 Then
'Get sheet name
TempWeekSheet = WeekdayName(DailyWeek)
'get the sales
Sales = ThisWorkbook.Sheets(TempWeekSheet).Range("N5").Val ue
'Used to get the column range starting with M
TempWeekRange = Chr(75 + DailyWeek)
If Sales 5 Then Sales = 5
'ThisWorkbook.Sheets("Settings").Range(TempWeekRan ge + "3").Value =
Format(Sheets("Settings").Range(TempWeekRange + "10").Value, "dd/mmm/
yyyy")
'ThisWorkbook.Sheets("Settings").Range(TempWeekRan ge + "4").Value =
Format(Sheets("Settings").Range(TempWeekRange + "11").Value, "dd/mmm/
yyyy")
'ThisWorkbook.Sheets("Settings").Range(TempWeekRan ge + "5").Value =
Format(Sheets("Settings").Range(TempWeekRange + "12").Value, "dd/mmm/
yyyy")
'ThisWorkbook.Sheets("Settings").Range(TempWeekRan ge + "6").Value =
Format(Sheets("Settings").Range(TempWeekRange + "13").Value, "dd/mmm/
yyyy")
'ThisWorkbook.Sheets("Settings").Range(TempWeekRan ge + "7").Value =
Format(Sheets("Settings").Range(TempWeekRange + "14").Value, "dd/mmm/
yyyy")
'ThisWorkbook.Sheets("Settings").Range(TempWeekRan ge + "8").Value =
Format(Sheets("Settings").Range(TempWeekRange + "15").Value, "dd/mmm/
yyyy")

ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "3" + ":" +
TempWeekRange + "8").NumberFormat = _
ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "10" + ":" +
TempWeekRange + "15").NumberFormat
ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "3" + ":" +
TempWeekRange + "8").Value = _
ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "10" + ":" +
TempWeekRange + "15").Value
'update list only if it is a prior weekday not today
If DailyWeek < Weekday(Date) Then
'check number of boxes, if no boxes were available then dont update
sales#
If ThisWorkbook.Sheets(TempWeekSheet).Range("N9").Val ue 0 Then
'ThisWorkbook.Sheets("Settings").Range(TempWeekRan ge + CStr(3 +
Sales)).Value = Format(Sheets("Settings").Range("E24").Value +
DailyWeek - 2, "dd/mmm/yyyy")
ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + CStr(3 +
Sales)).Value = CDate(Sheets("Settings").Range("E24").Value +
DailyWeek - 2)
End If
End If

Else
'Called by Monthly sheet or by end of the week sub
StartWeek = ThisWorkbook.Sheets("Settings").Range("E24").Value
EndWeek = StartWeek + SalesDateRange
If EndWeek StartWeek + 7 Then EndWeek = StartWeek + 5
'flag for new week or daily check from monthlysheet
If SalesDateRange = 8 Then
NewWeekFlag = 10
Else
NewWeekFlag = 3
End If

Do While StartWeek < EndWeek
TempWeekRange = Chr(75 + Weekday(StartWeek))
TempWeekSheet = WeekdayName(Weekday(StartWeek))
Sales = ThisWorkbook.Sheets(TempWeekSheet).Range("N5").Val ue
If Sales 5 Then Sales = 5
If ThisWorkbook.Sheets(TempWeekSheet).Range("N9").Val ue 0 Then
ThisWorkbook.Sheets("Settings").Range(TempWeekRang e +
CStr(NewWeekFlag + Sales)).Value = Format(StartWeek, "dd/mmm/yyyy")
End If
StartWeek = StartWeek + 1
Loop
If SalesDateRange = 8 Then
'funnily or not this one works.
ThisWorkbook.Sheets("Settings").Range("M3:R8").Num berFormat = _
ThisWorkbook.Sheets("Settings").Range("M10:R15").N umberFormat
ThisWorkbook.Sheets("Settings").Range("M3:R8").Val ue = _
ThisWorkbook.Sheets("Settings").Range("M10:R15").V alue
End If
End If
Application.ScreenUpdating = False
End Sub

--

Dave Peterson

--

Dave Peterson

Well I guess I will have to use

ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "10" + ":" +
TempWeekRange + "15").Copy _
Destination:=ThisWorkbook.Sheets("Settings").Range (TempWeekRange +
"10" + ":" + TempWeekRange + "15")
At least this one works... :)

Does my first suggestion work in a test workbook?

Are you sure this worked?

" wrote:

Well I guess I will have to use

ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "10" + ":" +
TempWeekRange + "15").Copy _
Destination:=ThisWorkbook.Sheets("Settings").Range (TempWeekRange +
"10" + ":" + TempWeekRange + "15")
At least this one works... :)

--

Dave Peterson

You code produces the same result even on an empty workbook.
The month and day are reversed.

The copy method does work, I've been testing it alot to make sure.
I dont understand why .value=.value does not work...
I wish someone would be able to explain this to me for future
reference.

On Jul 13, 4:02*pm, Dave Peterson wrote:
Does my first suggestion work in a test workbook?

Are you sure this worked?

" wrote:

Well I guess I will have to use

ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "10" + ":" +
TempWeekRange + "15").Copy _
* Destination:=ThisWorkbook.Sheets("Settings").Range (TempWeekRange +
"10" + ":" + TempWeekRange + "15")
At least this one works... :)

--

Dave Peterson

Try formatting your values using an unambiguous date format:

MMMM DD, YYYY

And then see what happens.

If the text of what you see in the cells doesn't change after you change the
format, then your values aren't really dates--it's just text that looks (to you)
like dates.

" wrote:

You code produces the same result even on an empty workbook.
The month and day are reversed.

The copy method does work, I've been testing it alot to make sure.
I dont understand why .value=.value does not work...
I wish someone would be able to explain this to me for future
reference.

On Jul 13, 4:02 pm, Dave Peterson wrote:
Does my first suggestion work in a test workbook?

Are you sure this worked?

" wrote:

Well I guess I will have to use

ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "10" + ":" +
TempWeekRange + "15").Copy _
Destination:=ThisWorkbook.Sheets("Settings").Range (TempWeekRange +
"10" + ":" + TempWeekRange + "15")
At least this one works... :)

--

Dave Peterson

--

Dave Peterson

ps. I'm curious what happens when you change the numberformat to that
unambiguous date format, but I bet your code will work if you use:

.value2 = .value2

Dave Peterson wrote:

Try formatting your values using an unambiguous date format:

MMMM DD, YYYY

And then see what happens.

If the text of what you see in the cells doesn't change after you change the
format, then your values aren't really dates--it's just text that looks (to you)
like dates.

" wrote:

You code produces the same result even on an empty workbook.
The month and day are reversed.

The copy method does work, I've been testing it alot to make sure.
I dont understand why .value=.value does not work...
I wish someone would be able to explain this to me for future
reference.

On Jul 13, 4:02 pm, Dave Peterson wrote:
Does my first suggestion work in a test workbook?

Are you sure this worked?

" wrote:

Well I guess I will have to use

ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "10" + ":" +
TempWeekRange + "15").Copy _
Destination:=ThisWorkbook.Sheets("Settings").Range (TempWeekRange +
"10" + ":" + TempWeekRange + "15")
At least this one works... :)

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Value2 does work!!! Finally.

I think you figured this out so dont make me wait any longer. :)

As for the ambigous format, yes it would change on the range M10, but
M3 range would be messed up the same way,

If the text of what you see in the cells doesn't change after you change the
format, then your values aren't really dates--it's just text that looks (to you)
like dates.

I thought of that, maybe the FORMAT in the line :
hisWorkbook.Sheets("Settings").Range(TempWeekRange +
CStr(NewWeekFlag + Sales)).Value = Format(StartWeek, "dd/mmm/yyyy")

is to blame?
I will switch it to CDATE instead and check it out.



On Jul 13, 7:52*pm, Dave Peterson wrote:
ps. *I'm curious what happens when you change the numberformat to that
unambiguous date format, but I bet your code will work if you use:

* *.value2 = .value2





Dave Peterson wrote:

Try formatting your values using an unambiguous date format:

MMMM DD, YYYY

And then see what happens.

If the text of what you see in the cells doesn't change after you change the
format, then your values aren't really dates--it's just text that looks (to you)
like dates.

" wrote:

You code produces the same result even on an empty workbook.
The month and day are reversed.

The copy method does work, I've been testing it alot to make sure.
I dont understand why .value=.value does not work...
I wish someone would be able to explain this to me for future
reference.

On Jul 13, 4:02 pm, Dave Peterson wrote:
Does my first suggestion work in a test workbook?

Are you sure this worked?

" wrote:

Well I guess I will have to use

ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "10" + ":" +
TempWeekRange + "15").Copy _
* Destination:=ThisWorkbook.Sheets("Settings").Range (TempWeekRange +
"10" + ":" + TempWeekRange + "15")
At least this one works... :)

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Yep.

If you use format(), then it's like typing the value into the cell.

And if your windows short date setting is mdy and you type: 1/2/2003, you'll
get Jan 2, 2003.

Even if you expected February, 1, 2003.

I think it's better to just assign the value (or .value2) and use the number
format to make it look pretty. Take excel's parsing routine completely out of
the process.

" wrote:

Value2 does work!!! Finally.

I think you figured this out so dont make me wait any longer. :)

As for the ambigous format, yes it would change on the range M10, but
M3 range would be messed up the same way,

If the text of what you see in the cells doesn't change after you change the
format, then your values aren't really dates--it's just text that looks (to you)
like dates.

I thought of that, maybe the FORMAT in the line :
hisWorkbook.Sheets("Settings").Range(TempWeekRange +
CStr(NewWeekFlag + Sales)).Value = Format(StartWeek, "dd/mmm/yyyy")

is to blame?
I will switch it to CDATE instead and check it out.

On Jul 13, 7:52 pm, Dave Peterson wrote:
ps. I'm curious what happens when you change the numberformat to that
unambiguous date format, but I bet your code will work if you use:

.value2 = .value2





Dave Peterson wrote:

Try formatting your values using an unambiguous date format:

MMMM DD, YYYY

And then see what happens.

If the text of what you see in the cells doesn't change after you change the
format, then your values aren't really dates--it's just text that looks (to you)
like dates.

" wrote:

You code produces the same result even on an empty workbook.
The month and day are reversed.

The copy method does work, I've been testing it alot to make sure.
I dont understand why .value=.value does not work...
I wish someone would be able to explain this to me for future
reference.

On Jul 13, 4:02 pm, Dave Peterson wrote:
Does my first suggestion work in a test workbook?

Are you sure this worked?

" wrote:

Well I guess I will have to use

ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "10" + ":" +
TempWeekRange + "15").Copy _
Destination:=ThisWorkbook.Sheets("Settings").Range (TempWeekRange +
"10" + ":" + TempWeekRange + "15")
At least this one works... :)

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Thanks for the help Dave.

What is the difference between .Value and .Value2 as an object?

Which Excel parsing routine are you talking about?
You are right about the dates, thats exactly what is happening.
I removed the Format and use Cdate instead anywhere a new date value
is added.
I think I will keep the copy line as well, it's working and I am tired
of messing with this. :)

Cheers

On Jul 13, 11:24*pm, Dave Peterson wrote:
Yep. *

If you use format(), then it's like typing the value into the cell.

And if your windows short date setting is mdy and you type: *1/2/2003, you'll
get Jan 2, 2003.

Even if you expected February, 1, 2003.

I think it's better to just assign the value (or .value2) and use the number
format to make it look pretty. *Take excel's parsing routine completely out of
the process.





" wrote:

Value2 does work!!! Finally.

I think you figured this out so dont make me wait any longer. :)

As for the ambigous format, yes it would change on the range M10, but
M3 range would be messed up the same way,

If the text of what you see in the cells doesn't change after you change the
format, then your values aren't really dates--it's just text that looks (to you)
like dates.

I thought of that, maybe the FORMAT in the line :
hisWorkbook.Sheets("Settings").Range(TempWeekRange +
CStr(NewWeekFlag + Sales)).Value = Format(StartWeek, "dd/mmm/yyyy")

is to blame?
I will switch it to CDATE instead and check it out.

On Jul 13, 7:52 pm, Dave Peterson wrote:
ps. *I'm curious what happens when you change the numberformat to that
unambiguous date format, but I bet your code will work if you use:

* *.value2 = .value2

Dave Peterson wrote:

Try formatting your values using an unambiguous date format:

MMMM DD, YYYY

And then see what happens.

If the text of what you see in the cells doesn't change after you change the
format, then your values aren't really dates--it's just text that looks (to you)
like dates.

" wrote:

You code produces the same result even on an empty workbook.
The month and day are reversed.

The copy method does work, I've been testing it alot to make sure..
I dont understand why .value=.value does not work...
I wish someone would be able to explain this to me for future
reference.

On Jul 13, 4:02 pm, Dave Peterson wrote:
Does my first suggestion work in a test workbook?

Are you sure this worked?

" wrote:

Well I guess I will have to use

ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "10" + ":" +
TempWeekRange + "15").Copy _
* Destination:=ThisWorkbook.Sheets("Settings").Range (TempWeekRange +
"10" + ":" + TempWeekRange + "15")
At least this one works... :)

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Cells with Dates/times and currencies are special in excel.

..value2 returns the underlying value (the serial number of the date) of the
cell.

VBA's help describes it in more detail.

" wrote:

Thanks for the help Dave.

What is the difference between .Value and .Value2 as an object?

Which Excel parsing routine are you talking about?
You are right about the dates, thats exactly what is happening.
I removed the Format and use Cdate instead anywhere a new date value
is added.
I think I will keep the copy line as well, it's working and I am tired
of messing with this. :)

Cheers

On Jul 13, 11:24 pm, Dave Peterson wrote:
Yep.

If you use format(), then it's like typing the value into the cell.

And if your windows short date setting is mdy and you type: 1/2/2003, you'll
get Jan 2, 2003.

Even if you expected February, 1, 2003.

I think it's better to just assign the value (or .value2) and use the number
format to make it look pretty. Take excel's parsing routine completely out of
the process.





" wrote:

Value2 does work!!! Finally.

I think you figured this out so dont make me wait any longer. :)

As for the ambigous format, yes it would change on the range M10, but
M3 range would be messed up the same way,

If the text of what you see in the cells doesn't change after you change the
format, then your values aren't really dates--it's just text that looks (to you)
like dates.

I thought of that, maybe the FORMAT in the line :
hisWorkbook.Sheets("Settings").Range(TempWeekRange +
CStr(NewWeekFlag + Sales)).Value = Format(StartWeek, "dd/mmm/yyyy")

is to blame?
I will switch it to CDATE instead and check it out.

On Jul 13, 7:52 pm, Dave Peterson wrote:
ps. I'm curious what happens when you change the numberformat to that
unambiguous date format, but I bet your code will work if you use:

.value2 = .value2

Dave Peterson wrote:

Try formatting your values using an unambiguous date format:

MMMM DD, YYYY

And then see what happens.

If the text of what you see in the cells doesn't change after you change the
format, then your values aren't really dates--it's just text that looks (to you)
like dates.

" wrote:

You code produces the same result even on an empty workbook.
The month and day are reversed.

The copy method does work, I've been testing it alot to make sure.
I dont understand why .value=.value does not work...
I wish someone would be able to explain this to me for future
reference.

On Jul 13, 4:02 pm, Dave Peterson wrote:
Does my first suggestion work in a test workbook?

Are you sure this worked?

" wrote:

Well I guess I will have to use

ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "10" + ":" +
TempWeekRange + "15").Copy _
Destination:=ThisWorkbook.Sheets("Settings").Range (TempWeekRange +
"10" + ":" + TempWeekRange + "15")
At least this one works... :)

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson