Hi All

I have a spreadsheet which manages to copy and paste its contents to another sheet within the workbook, My problem is I need this macro to also create a new sheet within the existing workbook based on the week number & type i.e. Sales or Units Week number 5 etc and everytime the week numer in cell "L2" is changed then it copies out to another new sheet under that week number and then paste that data into that week number?You will notice that after it transfers the data it clears the sheet I need it to copy to another new sheet before this happens?
Hope this explaains myself and what I'm trying to achieve?

P.s The macro is sitting on the "Consoildation" sheet to start with and copies data out to "data Entry" sheet

Can this be done? I have included the basic macro below

Sub transfersalesfigures()
'
' transfersalesfigures Macro
'

'
    Range("C2:I33").Select
    Selection.Copy
    Sheets("DATA ENTRY").Select
    Range("C2").Select
    ActiveSheet.Paste
    Range("C2").Select
    Sheets("CONSOLIDATION").Select
    Application.CutCopyMode = False
    Range("C2").Select
    Sheets("CONSOLIDATION").Select
    Range("C2:I33").Select
    Selection.ClearContents
    Range("C2").Select