PDA

View Full Version : VBA to do something 5 times then go back



jsneak4
04-20-2015, 01:58 AM
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 :think:

Thanks in advance!

MINCUS1308
04-21-2015, 12:28 PM
Its not exactly the same but this is a simple solution you might try out
13234
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
04-21-2015, 12:30 PM
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
04-21-2015, 11:55 PM
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.

13236

This is the spreadsheet that I'm currently using.

Sorry for being a pain :banghead:

jsneak4
04-22-2015, 12:06 AM
Sorry i've got it working.. Thank you so much!

MINCUS1308
04-22-2015, 03:47 AM
well you beat me too it. best of luck