VBA to do something 5 times then go back

[jsneak4]

Morning all,

I've tried everything and i can't get this to work.. Basically I have 5 sheet tabs (one for each day) On another tab i have a column of data.

What I’m asking is - Is it possible to paste the data (The column) into the corresponding day, but only 5 times (So basically it would take 5 weeks to fill a Wednesday) and once the 5th time is done, the next week would go back to column 1 ?

The code i have currently is below.

Option Explicit
Sub MoveDaily()
Dim i As Integer
   Dim strName As String
        Dim LC As Long
i = 0
    Sheets("Box Monitor by Batch").Select
    Range("F:F").Copy
    strName = Range("A1")
    Sheets(strName).Select
    LC = Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).Column
Cells(1, LC).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Do Until i = 10
i = i + 1
Debug.Print i
Loop
End Sub

This works to the point of knowing which day it is and pasting the data into the correct tab but what im wanting is to have an average column that never needs to adjust the formula. The above code just pastes the information to the right of my average column

Hope this makes sense

Thanks in advance!

[MINCUS1308]

Its not exactly the same but this is a simple solution you might try out
online.xlsm

Sub testing()

'SELECT THE CORRESPONDING SHEET BASED ON CELL B2 OF "Box Monitor by Batch"
    Sheets("Box Monitor by Batch").Select
    strName = Range("B2")
    
'DETERMINE WHAT ITTERATION ROW TO ADJUST ON THE SUPPORT SHEET
    Select Case strName
        Case "MONDAY"
            vROW = 3
        Case "TUESDAY"
            vROW = 4
        Case "WEDNESDAY"
            vROW = 5
        Case "THURSDAY"
            vROW = 6
        Case "FRIDAY"
            vROW = 7
    End Select


'ADJUST THE ITTERATION NUMBER
    If Sheets("SUPPORT").Cells(vROW, 3).Value = 5 Then
        Sheets("SUPPORT").Cells(vROW, 3).Value = 1
    Else
        Sheets("SUPPORT").Cells(vROW, 3).Value = Sheets("SUPPORT").Cells(vROW, 3).Value + 1
    End If
    
'COPY THE DATA ENTERED IN COLUMN 'F' OF "Box Monitor by Batch"
    Sheets("Box Monitor by Batch").Range("F:F").Copy


'PLACE THE DATA ON THE CORRECT SHEET
    Sheets(strName).Select
    Cells(1, Sheets("SUPPORT").Cells(vROW, 3).Value).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
End Sub

[MINCUS1308]

Basically your support tab will keep track of what day you are on. there are a hundred other ways to do this same thing but i thought this would be the easiest way for you to see whats happening

[jsneak4]

Thank you for your input, that works great! But i want it to automatically pick up the day - basically they are downloads that I put into my spreadsheet.

Forecast v2.xlsm

This is the spreadsheet that I'm currently using.

Sorry for being a pain

[jsneak4]

Sorry i've got it working.. Thank you so much!

[MINCUS1308]

well you beat me too it. best of luck