VBA for moving certain worksheets within the same workbook

[alyos]

Hi all,

Haven't worked with VBA for some time - and unfortunately forgot even the basics, so your help here will be highly appreciated. Went through a few threads, couldn't find what I'm looking for...

Here is what I'm trying to achieve:

1) I have 14 visible worksheets to capture 12 months period (with Start and End worksheets being empty).

2) When I have data for new month, I need to: select the latest month (always before 'End' tab - in this example Jun 22), create a copy of it and move the copy after 'End' tab. I then need to return to the original tab (Jun 22), select cells range (B16:I16) and perform PasteSpecial xlPasteValues.

3)After that the copy (Jun 22 (2)) that was moved after 'End' needs to be moved before 'End' so it looks like this:

4) Next step is to move whatever month is after 'Start' (Jul 21 in this example), before 'Start'. Once moved, I need to hide 'Jul 21' tab, so that I end up with another 12 months overview.

I do realise that I will need variables for this task, or setting 'Start' and 'End' as active sheets since they are the main criteria and other sheets' names will be always different, but that's as far as my understanding goes

Many thanks for your help and please shout if the above doesn't make sense at all.

 

[mumps]

Try:

Sub MoveSheets()
    Application.ScreenUpdating = False
    Sheets(Sheets("End").Index - 1).Copy after:=Sheets(Sheets.Count)
    Sheets(Sheets("End").Index - 1).Range("B16:I16").Value = Sheets(Sheets("End").Index - 1).Range("B16:I16").Value
    Sheets(Sheets.Count).Move Before:=Sheets("End")
    Sheets(Sheets("Start").Index + 1).Move Before:=Sheets("Start")
    Sheets(Sheets("Start").Index - 1).Visible = False
    Application.ScreenUpdating = True
End Sub

 

[alyos]

Try:

Sub MoveSheets()
    Application.ScreenUpdating = False
    Sheets(Sheets("End").Index - 1).Copy after:=Sheets(Sheets.Count)
    Sheets(Sheets("End").Index - 1).Range("B16:I16").Value = Sheets(Sheets("End").Index - 1).Range("B16:I16").Value
    Sheets(Sheets.Count).Move Before:=Sheets("End")
    Sheets(Sheets("Start").Index + 1).Move Before:=Sheets("Start")
    Sheets(Sheets("Start").Index - 1).Visible = False
    Application.ScreenUpdating = True
End Sub

Thanks so much - all resolved with your help!

 

[mumps]

You are very welcome.