Hi

I am using the below code that I got off this forum. All works to my
requirements, except, in cell L31 on all of my sheets in the workbook shows
the value as a percentage, e.g. 31.16%
When runing the macro, I get the value in the summary sheet as 0.311615
Q. What alteration is required to the code and where abouts it be placed to
show the correct format?
I am not a code writer, nor understand them and only get by with copy and
paste, and therefore any help would be appriciated

thanks in advance

Brian

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200612/1

brian thompson3001 wrote:
Hi

I am using the below code that I got off this forum. All works to my
requirements, except, in cell L31 on all of my sheets in the workbook shows
the value as a percentage, e.g. 31.16%
When runing the macro, I get the value in the summary sheet as 0.311615
Q. What alteration is required to the code and where abouts it be placed to
show the correct format?
I am not a code writer, nor understand them and only get by with copy and
paste, and therefore any help would be appriciated

thanks in advance


Sub Summary()
Dim ws As Worksheet
Dim i As Integer
i = 2
Sheets(1).Activate
Sheets.Add
With Sheets(1)
.Range("A1").Value = "Week"
.Range("B1").Value = "Released"
.Range("C1").Value = "Delivered"
.Range("D1").Value = "Del Late"
.Range("E1").Value = "Performance"
For Each ws In ThisWorkbook.Worksheets
If ws.Index < 1 Then
.Rows(i).Cells(1).Value = ws.Name
.Rows(i).Cells(2).Value = ws.Range("L30")
.Rows(i).Cells(3).Value = ws.Range("L27")
.Rows(i).Cells(4).Value = ws.Range("L29")
.Rows(i).Cells(5).Value = ws.Range("L31")
i = i + 1
End If
Next
End With
End Sub



Brian

--
bnt

Message posted via http://www.officekb.com

I think that you want

Sub Summary()
Dim ws As Worksheet
Dim i As Integer
i = 2
Sheets(1).Activate
Sheets.Add
With Sheets(1)
.Range("A1").Value = "Week"
.Range("B1").Value = "Released"
.Range("C1").Value = "Delivered"
.Range("D1").Value = "Del Late"
.Range("E1").Value = "Performance"
For Each ws In ThisWorkbook.Worksheets
If ws.Index < 1 Then
.Rows(i).Cells(1).Value = ws.Name
.Rows(i).Cells(2).Value = ws.Range("L30")
.Rows(i).Cells(3).Value = ws.Range("L27")
.Rows(i).Cells(4).Value = ws.Range("L29")
.Rows(i).Cells(5).Value = Format(ws.Range("L31").Value, "0.00%")
i = i + 1
End If
Next
End With
End Sub



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"brian thompson3001 via OfficeKB.com" <u15682@uwe wrote in message
news:6b0a26dd14ab9@uwe...
brian thompson3001 wrote:
Hi

I am using the below code that I got off this forum. All works to my
requirements, except, in cell L31 on all of my sheets in the workbook
shows
the value as a percentage, e.g. 31.16%
When runing the macro, I get the value in the summary sheet as 0.311615
Q. What alteration is required to the code and where abouts it be placed
to
show the correct format?
I am not a code writer, nor understand them and only get by with copy and
paste, and therefore any help would be appriciated

thanks in advance


Sub Summary()
Dim ws As Worksheet
Dim i As Integer
i = 2
Sheets(1).Activate
Sheets.Add
With Sheets(1)
.Range("A1").Value = "Week"
.Range("B1").Value = "Released"
.Range("C1").Value = "Delivered"
.Range("D1").Value = "Del Late"
.Range("E1").Value = "Performance"
For Each ws In ThisWorkbook.Worksheets
If ws.Index < 1 Then
.Rows(i).Cells(1).Value = ws.Name
.Rows(i).Cells(2).Value = ws.Range("L30")
.Rows(i).Cells(3).Value = ws.Range("L27")
.Rows(i).Cells(4).Value = ws.Range("L29")
.Rows(i).Cells(5).Value = ws.Range("L31")
i = i + 1
End If
Next
End With
End Sub



Brian

--
bnt

Message posted via http://www.officekb.com

thanks bob, works perfect

Merry Christmas - VBG

Brian

Bob Phillips wrote:
I think that you want

Sub Summary()
Dim ws As Worksheet
Dim i As Integer
i = 2
Sheets(1).Activate
Sheets.Add
With Sheets(1)
.Range("A1").Value = "Week"
.Range("B1").Value = "Released"
.Range("C1").Value = "Delivered"
.Range("D1").Value = "Del Late"
.Range("E1").Value = "Performance"
For Each ws In ThisWorkbook.Worksheets
If ws.Index < 1 Then
.Rows(i).Cells(1).Value = ws.Name
.Rows(i).Cells(2).Value = ws.Range("L30")
.Rows(i).Cells(3).Value = ws.Range("L27")
.Rows(i).Cells(4).Value = ws.Range("L29")
.Rows(i).Cells(5).Value = Format(ws.Range("L31").Value, "0.00%")
i = i + 1
End If
Next
End With
End Sub

Hi

[quoted text clipped - 37 lines]

Brian

--
bnt

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200612/1

Bob

one last thing,

If I wanted the sheet that the macro has generated to be named "Summary
Sheet", What would be the coding? and same again where to place it.

regards

brian thompson3001 wrote:
thanks bob, works perfect

Merry Christmas - VBG

Brian

I think that you want

[quoted text clipped - 28 lines]

Brian


--
bnt

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200612/1

Change the line

Sheets.Add

to

Worksheets.Add.name = "Summary"

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"brian thompson3001 via OfficeKB.com" <u15682@uwe wrote in message
news:6b0ab4db1d2e6@uwe...
Bob

one last thing,

If I wanted the sheet that the macro has generated to be named "Summary
Sheet", What would be the coding? and same again where to place it.

regards

brian thompson3001 wrote:
thanks bob, works perfect

Merry Christmas - VBG

Brian

I think that you want

[quoted text clipped - 28 lines]

Brian


--
bnt

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200612/1