Dear All,
I need to generate serial number in the summary sheet.
I have a summary sheet and a template (hidden). Macro is written to create copy of template and has to pick few details from the newly created idea template to the summary sheet.Till here its working fine. once it copies details from new idea sheet to summary sheet it gives sl. No in column A. The problem here is , every time first created idea is pushed down as the new one comes in.
How should fix it so that to send new idea to the last row every time. here is my macro
Sub Summary()
'Sheets.Add
Dim objWS As Worksheet
Dim intX As Integer
intX = 0
Cells(4, 1) = "Sl.No"
Cells(4, 2) = "Idea"
Cells(4, 3) = "Opportunity Savings"
Cells(4, 4) = "Investment"
Cells(4, 5) = "Pay Back"
'Cells(4, 6) = "Status"
'Cells(4, 7) = "Opp Assessment"
'MsgBox Worksheets.Count
For Each objWS In Worksheets
If objWS.Name <> "Summary" Then
Cells(5 + intX, 1) = intX + 0 ' SI #
Cells(5 + intX, 2) = objWS.Cells(2, 4) ' idea
Cells(5 + intX, 3) = objWS.Cells(16, 21) ' Opp savings
Cells(5 + intX, 4) = objWS.Cells(17, 22) ' Investment
Cells(5 + intX, 5) = objWS.Cells(18, 21) ' Payback
'Cells(5 + intX, 6) = objWS.Cells(46, 18) ' Status
'Cells(5 + intX, 4) = objWS.Cells(16, 20) ' Benefits
Cells(5 + intX, 6) = objWS.Cells(19, 21) 'Opp Assessment
intX = intX + 1
End If
Next
Rows("5:5").Select
Selection.EntireRow.Hidden = True
Range("A1").Select
End Sub
Thanking you in advance...
Dee...
I need to generate serial number in the summary sheet.
I have a summary sheet and a template (hidden). Macro is written to create copy of template and has to pick few details from the newly created idea template to the summary sheet.Till here its working fine. once it copies details from new idea sheet to summary sheet it gives sl. No in column A. The problem here is , every time first created idea is pushed down as the new one comes in.
How should fix it so that to send new idea to the last row every time. here is my macro
Sub Summary()
'Sheets.Add
Dim objWS As Worksheet
Dim intX As Integer
intX = 0
Cells(4, 1) = "Sl.No"
Cells(4, 2) = "Idea"
Cells(4, 3) = "Opportunity Savings"
Cells(4, 4) = "Investment"
Cells(4, 5) = "Pay Back"
'Cells(4, 6) = "Status"
'Cells(4, 7) = "Opp Assessment"
'MsgBox Worksheets.Count
For Each objWS In Worksheets
If objWS.Name <> "Summary" Then
Cells(5 + intX, 1) = intX + 0 ' SI #
Cells(5 + intX, 2) = objWS.Cells(2, 4) ' idea
Cells(5 + intX, 3) = objWS.Cells(16, 21) ' Opp savings
Cells(5 + intX, 4) = objWS.Cells(17, 22) ' Investment
Cells(5 + intX, 5) = objWS.Cells(18, 21) ' Payback
'Cells(5 + intX, 6) = objWS.Cells(46, 18) ' Status
'Cells(5 + intX, 4) = objWS.Cells(16, 20) ' Benefits
Cells(5 + intX, 6) = objWS.Cells(19, 21) 'Opp Assessment
intX = intX + 1
End If
Next
Rows("5:5").Select
Selection.EntireRow.Hidden = True
Range("A1").Select
End Sub
Thanking you in advance...
Dee...