Consulting

Results 1 to 6 of 6

Thread: VBA to do something 5 times then go back

  1. #1
    VBAX Newbie
    Joined
    Apr 2015
    Posts
    3
    Location

    VBA to do something 5 times then go back

    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!

  2. #2
    VBAX Tutor MINCUS1308's Avatar
    Joined
    Jun 2014
    Location
    UNDER MY DESK
    Posts
    253
    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
    - I HAVE NO IDEA WHAT I'M DOING

  3. #3
    VBAX Tutor MINCUS1308's Avatar
    Joined
    Jun 2014
    Location
    UNDER MY DESK
    Posts
    253
    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
    - I HAVE NO IDEA WHAT I'M DOING

  4. #4
    VBAX Newbie
    Joined
    Apr 2015
    Posts
    3
    Location
    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

  5. #5
    VBAX Newbie
    Joined
    Apr 2015
    Posts
    3
    Location
    Sorry i've got it working.. Thank you so much!

  6. #6
    VBAX Tutor MINCUS1308's Avatar
    Joined
    Jun 2014
    Location
    UNDER MY DESK
    Posts
    253
    well you beat me too it. best of luck
    - I HAVE NO IDEA WHAT I'M DOING

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •