Hi,
Please i ask for help as i have a sheet that i update daily, with each update an VBA migrate it to a new sheet at the same workbook, this code use a specific cell "contain date with this format ("dd-mmm-yyyy") eg
17-Jul-2014) as a title to the new sheet.
i want to migrate this sheets update to a new workbook and this workbook title will be the titled sheet month "Jul-2014". With the first sheet in a new month, want to migrate it to a new workbook titled with its month and contain the month update sheets. want new workbook for each new month
the main sheet that the data will copied from is called "Preview", its copied range will be from ("A1:DA500"), this data wil pasted as "Values and number formats". and will save at the Desktop
this is the code that i use to paste to a new sheet
Please i ask for help as i have a sheet that i update daily, with each update an VBA migrate it to a new sheet at the same workbook, this code use a specific cell "contain date with this format ("dd-mmm-yyyy") eg
i want to migrate this sheets update to a new workbook and this workbook title will be the titled sheet month "Jul-2014". With the first sheet in a new month, want to migrate it to a new workbook titled with its month and contain the month update sheets. want new workbook for each new month
the main sheet that the data will copied from is called "Preview", its copied range will be from ("A1:DA500"), this data wil pasted as "Values and number formats". and will save at the Desktop
this is the code that i use to paste to a new sheet
Code:
Sub CopyNew()
Dim wsNew As Worksheet
Dim myRange As Range
Dim strName As String
Application.ScreenUpdating = False
With Worksheets("preview")
'Can't use / in sheet names, changed format:
strName = Format(.Range("C2").Value, "dd-mmm-yyyy")
Set myRange = .Range("A1:DA500")
Set wsNew = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets("preview"))
wsNew.Name = strName
myRange.Copy
wsNew.Cells(1, 1).PasteSpecial xlPasteValues
wsNew.Cells(1, 1).PasteSpecial xlPasteFormats
wsNew.Cells(1, 1).PasteSpecial xlPasteColumnWidths
Application.CutCopyMode = False
Call SortSheets
.Move Before:=Sheets(1) 'Move the "Final" sheet to the be the first
End With
Application.ScreenUpdating = True
End Sub
Sub SortSheets()
Dim i As Long, j As Long
For i = 1 To Sheets.Count
For j = 1 To Sheets.Count - 1
If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then
Sheets(j).Move After:=Sheets(j + 1)
End If
Next j
Next i
End Sub