How to create auto sheets and give same headings for all

Closed
Chaitucool4u
Posts
2
Registration date
Thursday January 26, 2017
Status
Member
Last seen
February 7, 2017
- Feb 2, 2017 at 01:32 PM
TrowaD
Posts
2884
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 21, 2022
- Feb 13, 2017 at 11:40 AM
Hello,

I have created one macro, by clicking on a button there are multiple sheets created automatically and naming the sheets with the calendar days.

Now the problem is that i need to give labels for all those sheets at one go.

Can you please help in write macro for me.

I have used below macro for inserting the sheets and now i need to include the labels in the same macro.

Sub DoDays()
Dim J As Integer
Dim K As Integer
Dim sDay As String
Dim sTemp As String
Dim iTarget As Integer
Dim dBasis As Date

iTarget = 13
While (iTarget < 1) Or (iTarget > 12)
iTarget = Val(InputBox("Numeric month?"))
If iTarget = 0 Then Exit Sub
Wend

Application.ScreenUpdating = False
sTemp = Str(iTarget) & "/1/" & Year(Now())
dBasis = CDate(sTemp)

For J = 1 To 31
sDay = Format((dBasis + J - 1), "dd-mm-yy")
If Month(dBasis + J - 1) = iTarget Then

If J <= Sheets.Count Then
If Left(Sheets(J).Name, 5) = "Sheet" Then
Sheets(J).Name = sDay
Else
Sheets.Add.Move after:=Sheets(Sheets.Count)
ActiveSheet.Name = sDay
End If
Else
Sheets.Add.Move after:=Sheets(Sheets.Count)
ActiveSheet.Name = sDay
End If
End If
Next J

For J = 1 To (Sheets.Count - 1)
For K = J + 1 To Sheets.Count
If Right(Sheets(J).Name, 10) > _
Right(Sheets(K).Name, 10) Then
Sheets(K).Move Before:=Sheets(J)
End If
Next K
Next J

Sheets(1).Activate
Application.ScreenUpdating = True
End Sub


I need below labels in row to be given for all these auto sheets populated.

1. System 2. CoCd 3. Customer 4. Doc.no. 5. Itm 6. Doc. Type 7. Assignment 8. Year 9. Reference 10. Text 11. Curr. 12. Pstg date 13. Entry dte 14. Amount 15. Responsible 16. Days under AR's control 17. Day sent to CAM 18. Days under CAM's control 19. Category 20. Comments 21. EOD Status 22. Comments


Thanks,

Regards,
Chaitanya.CH

2 replies

TrowaD
Posts
2884
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 21, 2022
512
Feb 7, 2017 at 11:46 AM
Hi Chaitanya,

Create a template sheet with all the headers in place and then instead of adding a new sheet, copy the template sheet and rename it.

Replace:
Sheets.Add.Move after:=Sheets(Sheets.Count) 
ActiveSheet.Name = sDay

With:
Sheets("Template").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = sDay

When you don't like to see the template sheet, then use this:
Sheets("Template").Visible = True
Sheets("Template").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = sDay
Sheets("Template").Visible = False


Best regards,
Trowa
0
Chaitucool4u
Posts
2
Registration date
Thursday January 26, 2017
Status
Member
Last seen
February 7, 2017

Feb 7, 2017 at 01:45 PM
Hello TrowaD,

I used that mentioned script but im getting error messgae.

Please share your email ID so that i can forward my excel sheet for better understanding.

Regards,
Chaitanya.Ch
0
TrowaD
Posts
2884
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 21, 2022
512
Feb 13, 2017 at 11:40 AM
Hi Chaitanya,

Make sure you named your template sheet "Template".

If your still having trouble you can upload (careful with sensitive data) your workbook to a free filesharing site like www.speedyshare.com or ge.tt and post back the download link.

Best regards,
Trowa
0