Archive old weeks in Schedule file

you can simply hide all the old columns after reading them. select the columns and right click and select ' Hide' .

bhos123 said:

you can simply hide all the old columns after reading them. select the columns and right click and select ' Hide' .

Click to expand...

Hi, thanks for your reply. I do understand that I could just hide the columns but it wouldn't work well for us. It would make everything much eaiser if we could cut it out and paste it on another sheet instead.

to automate this, what all columns should we work on? and can you provide the column names and column positions if possible.

bhos123 said:

to automate this, what all columns should we work on? and can you provide the column names and column positions if possible.

Click to expand...

Hi, I'll attach a picture of how it looks like now. One day for each column, starting from A and expanding to the right untill the end of the year

run the below macro.I have kept the name as Sheet1.

Sub latest()
Dim LastCol As Integer
Sheets("Sheet1").Select
With ActiveSheet
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
Sheets("Sheet1").Select
Sheets("Sheet1").Copy Before:=Sheets(1)
Sheets("Sheet1 (2)").Select
Sheets("Sheet1 (2)").Name = "Archive"
Sheets("Sheet1").Select
Range(Cells(, 1), Cells(, LastCol - 1)).Select
Selection.Delete Shift:=xlToLeft
End Sub

bhos123 said:

run the below macro.I have kept the name as Sheet1.

Sub latest()
Dim LastCol As Integer
Sheets("Sheet1").Select
With ActiveSheet
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
Sheets("Sheet1").Select
Sheets("Sheet1").Copy Before:=Sheets(1)
Sheets("Sheet1 (2)").Select
Sheets("Sheet1 (2)").Name = "Archive"
Sheets("Sheet1").Select
Range(Cells(, 1), Cells(, LastCol - 1)).Select
Selection.Delete Shift:=xlToLeft
End Sub

Click to expand...

Hi! Thanks for the code, it does copy it to a new sheet, however there are some vital things missing.

1. It can only run once because it says the name is taken everytime you want to archive it
2. I would want it to also delete all the days untill the present week.

So if you click the button it copies everything from week 1 to week 7 since thats the current week and then Archive that. in the Archive sheet. Then it deletes those weeks in the Sheet1.

I need more details to provide you the code. when your data gets updated, does the update happen on the same sheet that you have archived. Or you will get a complete new file.

give me some more details with logical flow so that I can prepare the sequential algorithm. consider an example and give me the detail operations in sequentially.

bhos123 said:

I need more details to provide you the code. when your data gets updated, does the update happen on the same sheet that you have archived. Or you will get a complete new file.

give me some more details with logical flow so that I can prepare the sequential algorithm. consider an example and give me the detail operations in sequentially.

Click to expand...

I'll do my best to explain everything.

2 sheets, one "main" sheet and one "Archive".

In the main sheet we update with new dates, we assign work periods (Night shift, morning shift & day shift, the "K" "M" "D" that you see in the printscreen) We can also put in vacations "Semester" (The black bars).
The Main sheet will in other words be updated and changed every now and then. So what I wish to be displayed on the main sheet is, starting from column A, the current week and future upcomming weeks.

In the archive I want all the weeks that has passed to be. So when you for example press the "Archive button" or however you want to do it, all the old weeks that has already passed in the "Main" sheet will be cut out and put in the Archive sheet. I want everything to happen in the same file. I do not want seperate files for this I hope this explains enough for you! Poke me if you need more to work with! I appreciate the help alot!

Well.. This gives me some direction to look at. How should I differentiate the current week, previous week and future weeks?? do you have any column names with dates?

somehow the pictures not getting loaded in my computer. the reason why I am asking more details from you.