Copy data to New Sheet Daily 1

[robcarr]

Hi All,

I have a workbook, created each week and I need to copy the data from 1 file to this weekly workbook on a daily basis, Each time the data is copied I need to copy the data to a new sheet and the sheet name has to be the previous days date, my weeks go saturdays-fridays. Does anybody now how to do this, I think it has something to do with the offset option using add workbook, but not really sure.

Any help will be greatly appreciated.
Rob.

 

[rocknrisk]

Hey Archie, "Finish what you started"

 

[uberpudge]

Hello Rob,

This is how I would do what you want to do:

Public Sub Test()
Dim wkbkNewDaily As Worksheet

'Worksheets.Add will return a worksheet object referencing the newly created
'sheet. The Worksheets.Add method uses the following syntax:
'
' expression.Add(Before, After, Count, Type)
'
' expression Required. An expression that returns a Worksheets object.
' Before Optional Variant. An object that specifies the sheet before which
' the new sheet is added.
' After Optional Variant. An object that specifies the sheet after which
' the new sheet is added.
' Count Optional Variant. The number of sheets to be added. The default
' value is one.
' Type Optional Variant. The sheet type. Can be one of the following
' XlSheetType constants: xlWorksheet, xlExcel4MacroSheet, or
' xlExcel4IntlMacroSheet. The default value is xlWorksheet.
'
' Remarks
' If Before and After are both omitted, the new sheet is inserted before the
' active sheet.

Set wkbkNewDaily = Worksheets.Add(, ActiveSheet, 1, XlSheetType.xlWorksheet)
wkbkNewDaily.Name = Strings.Format(Conversion.CStr(DateTime.Now()), "mm-dd-yyyy&quot

Worksheets("Sheet1&quot.Range("A1:C25&quot.Copy
wkbkNewDaily.Range("A1:C25&quot.PasteSpecial XlPasteType.xlPasteAll

Set wkbkNewDaily = Nothing
End Sub

This worked when I tested it.
Just copy, paste and modify where needed.
Hope this helps,
Pete