Code doesn't generate correctly on two of 37 instances
 

Okay, this is really frustrating. I have code that auto generates 37
worksheets, copying data from a master worksheet.Then it adds some columns
and some formulas. On two of the 37 worksheets, when it adds the columns,
the column headings appear diferently. I'll try to explain and cover any
questions.

The data is for the same 37 people and the autogenerated worksheets are for
each person.

Code starts by deleting old data using the clear function. Then it imports
data and formats it. Then it autogenerates the 37 worksheets, copies the
data from the master sheet and then adds columns. here's the code for that
part.

Columns("E:E").Select
Selection.Insert Shift:=xlToRight ***(12 columns are inserted)

Range("E2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[20]:RC[24])"
MntTot = ActiveCell.Value
ActiveCell.Offset(-1, 0).Value = "Jan " + CStr(MntTot) + ""
Range("F2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[24]:RC[27])"
MntTot = ActiveCell.Value
ActiveCell.Offset(-1, 0).Value = "Feb " + CStr(MntTot) + ""
*****does this for all months

The code then adds two charts. It's the charts that show me the problem.
The problem on the two sheets (and it's the same two sheets everytime) is
that the column headings show up 'funny'.

The good sheets have the chart axis show up as the months plus the number
specified in the code above. The bad sheets axis' show up like this:
Feb-60, Feb-61, Feb-63 (there are 16 of these).

So why, when I'm basically starting out with new worksheets and new data
does the code mess up on only 2 of the worksheets? The worksheets are #31
and #37. I can find no 'unique' cirmcumstances for these two worksheets.

Code doesn't generate correctly on two of 37 instances
 

Whatever MntTot is for those two sheets, it looks like a date to Excel.

You can fix it with a single quote at the beginning as below

ActiveCell.Offset(-1, 0).Value = "'Jan " + CStr(MntTot) + ""

Single quote added before Jan. The single quote won't be visible except in
the formula bar.

--
Regards,
Tom Ogilvy


"Rominall" wrote:

Okay, this is really frustrating. I have code that auto generates 37
worksheets, copying data from a master worksheet.Then it adds some columns
and some formulas. On two of the 37 worksheets, when it adds the columns,
the column headings appear diferently. I'll try to explain and cover any
questions.

The data is for the same 37 people and the autogenerated worksheets are for
each person.

Code starts by deleting old data using the clear function. Then it imports
data and formats it. Then it autogenerates the 37 worksheets, copies the
data from the master sheet and then adds columns. here's the code for that
part.

Columns("E:E").Select
Selection.Insert Shift:=xlToRight ***(12 columns are inserted)

Range("E2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[20]:RC[24])"
MntTot = ActiveCell.Value
ActiveCell.Offset(-1, 0).Value = "Jan " + CStr(MntTot) + ""
Range("F2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[24]:RC[27])"
MntTot = ActiveCell.Value
ActiveCell.Offset(-1, 0).Value = "Feb " + CStr(MntTot) + ""
*****does this for all months

The code then adds two charts. It's the charts that show me the problem.
The problem on the two sheets (and it's the same two sheets everytime) is
that the column headings show up 'funny'.

The good sheets have the chart axis show up as the months plus the number
specified in the code above. The bad sheets axis' show up like this:
Feb-60, Feb-61, Feb-63 (there are 16 of these).

So why, when I'm basically starting out with new worksheets and new data
does the code mess up on only 2 of the worksheets? The worksheets are #31
and #37. I can find no 'unique' cirmcumstances for these two worksheets.

Code doesn't generate correctly on two of 37 instances
 

I just knew it would be something simple. THANKS!!

"Rominall" wrote:

Okay, this is really frustrating. I have code that auto generates 37
worksheets, copying data from a master worksheet.Then it adds some columns
and some formulas. On two of the 37 worksheets, when it adds the columns,
the column headings appear diferently. I'll try to explain and cover any
questions.

The data is for the same 37 people and the autogenerated worksheets are for
each person.

Code starts by deleting old data using the clear function. Then it imports
data and formats it. Then it autogenerates the 37 worksheets, copies the
data from the master sheet and then adds columns. here's the code for that
part.

Columns("E:E").Select
Selection.Insert Shift:=xlToRight ***(12 columns are inserted)

Range("E2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[20]:RC[24])"
MntTot = ActiveCell.Value
ActiveCell.Offset(-1, 0).Value = "Jan " + CStr(MntTot) + ""
Range("F2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[24]:RC[27])"
MntTot = ActiveCell.Value
ActiveCell.Offset(-1, 0).Value = "Feb " + CStr(MntTot) + ""
*****does this for all months

The code then adds two charts. It's the charts that show me the problem.
The problem on the two sheets (and it's the same two sheets everytime) is
that the column headings show up 'funny'.

The good sheets have the chart axis show up as the months plus the number
specified in the code above. The bad sheets axis' show up like this:
Feb-60, Feb-61, Feb-63 (there are 16 of these).

So why, when I'm basically starting out with new worksheets and new data
does the code mess up on only 2 of the worksheets? The worksheets are #31
and #37. I can find no 'unique' cirmcumstances for these two worksheets.