Creating monthly report and moving data from mainsheet.

[PerttiPP]

Hi,
I'm trying to create VBA code that creates daily sheet and moves data to new sheet when running code. For example, I'm running today the code in the main sheet, it will automatically create sheet called "report 12.11.2021" and move the data from main sheet there.
The problem is that I can't find the solution to move the data from mainsheet to new created sheet. After this problem is solved, I'm able to create analysis and fix the data...

Creating new "report sheet" code below:

Sub MonthlyWorksheets()

' This macro places new worksheet every month in the workbook
Dim i As Integer

i = 1

Dim theDate As Date
theDate = Date
Debug.Print theDate



Do While i <= 1
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "Report " & theDate

i = i + 1
Loop



End Sub

 

[Zot]

Something like this. Need to change date format because slash / is illegal as name in VBA

Sub MonthlyWorksheets()

' This macro places new worksheet every month in the workbook
Dim theDate As Date
Dim wsMain As Worksheet, wsTarget As Worksheet

Application.ScreenUpdating = False

theDate = Date

Set wsMain = ActiveWorkbook.Sheets("Main")

Set wsTarget = Sheets.Add(After:=Sheets(Sheets.Count))
wsTarget.Name = "Report " & Format(theDate, "dd-mm-yyyy")

wsMain.Cells.Copy wsTarget.Range("A1")

End Sub

 

[My Aswer Is This]

Your subject title say: Creating monthly report

But then you said:
I'm trying to create VBA code that creates daily sheet
So will you be creating a new sheet every day or once a Month.
And name the sheet the Date the new sheet was created .
Because you wanted to name your new sheet Date and not Month.

 

[PerttiPP]

Your subject title say: Creating monthly report

But then you said:
I'm trying to create VBA code that creates daily sheet
So will you be creating a new sheet every day or once a Month.
And name the sheet the Date the new sheet was created .
Because you wanted to name your new sheet Date and not Month.

Sorry for bad explanation.

 

[PerttiPP]

Something like this. Need to change date format because slash / is illegal as name in VBA

Sub MonthlyWorksheets()

' This macro places new worksheet every month in the workbook
Dim theDate As Date
Dim wsMain As Worksheet, wsTarget As Worksheet

Application.ScreenUpdating = False

theDate = Date

Set wsMain = ActiveWorkbook.Sheets("Main")

Set wsTarget = Sheets.Add(After:=Sheets(Sheets.Count))
wsTarget.Name = "Report " & Format(theDate, "dd-mm-yyyy")

wsMain.Cells.Copy wsTarget.Range("A1")

End Sub

Thank you! This was what I was looking for!

 

[My Aswer Is This]

Here is how I would do it:
Creates a copy of sheet name Master and renames it with todays date.

Sub Copy_Sheet()
'Modified  11/12/2021  4:17:01 AM  EST
Application.ScreenUpdating = False
On Error GoTo M
Sheets("Master").Copy After:=Sheets(Sheets.Count): ActiveSheet.Name = Format(Date, "MMM-DD-YYYY")
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "That sheet name may already exist" & vbNewLine & "Or may be a improper Name"
End Sub