Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

How to create auto sheets and give same headings for all [Closed]

Report
Posts
2
Registration date
Thursday January 26, 2017
Status
Member
Last seen
February 7, 2017
 -
Posts
2676
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 3, 2020
 -
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
See more

2 replies

Answer 1 / 2
Posts
2676
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 3, 2020
 448
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
Report
Posts
2
Registration date
Thursday January 26, 2017
Status
Member
Last seen
February 7, 2017
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
Answer 2 / 2
Posts
2676
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 3, 2020
 448
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
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!