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]

I don't think that you need the "Start" and "End" sheets to do what you want. Would those two sheets be needed for some other reason?

 

[alyos]

I don't think that you need the "Start" and "End" sheets to do what you want. Would those two sheets be needed for some other reason?

Hi,
That's how the spreadsheet is set up at the moment - each month has a formula which summarises data for 12 months period, e.g of formula =SUM(Start:End!B8)

 

[mumps]

Try:

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

 

[alyos]

Try:

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

This worked perfectly! It captures everything up to my 4) point; is there any chance you could help with the remaining bit, where the tab after 'Start' has to be moved before 'Start' and be hidden?

Thanks so much!

 

[mumps]

the tab after 'Start' has to be moved before 'Start' and be hidden

I believe that the code already does that.

 

[alyos]

I believe that the code already does that.

That's how it looks to me as well, however, when I run it:
1) before running

2) after running it goes to the 'Start' tab and finishes there without moving Jul 21

Do you happen to know the reason behind it?

 

[alyos]

That's how it looks to me as well, however, when I run it:
1) before running
View attachment 69880

2) after running it goes to the 'Start' tab and finishes there without moving Jul 21
View attachment 69881

Do you happen to know the reason behind it?

Maybe it's worth noting that I do have a few hidden tabs already before 'Start'? Could that be the reason?

 

[mumps]

Definitely. How many sheets are hidden and where are they located? Post a picture that shows all sheets.

 

[alyos]

Definitely. How many sheets are hidden and where are they located? Post a picture that shows all sheets.

All of hidden sheets are located before 'Start' and in total it's 66 of them (wouldn't be able to post a picture with all of them included, but that's how it looks like once unhidden:

So it starts from Oct 15 (the very first tab) until year to date pretty much. I hope this helps?