Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jul 2014
    Posts
    33
    Thanks
    7
    Thanked 3 Times in 3 Posts

    Posting a row of Data Into Range until full

    Okay another post here...

    Basically I would like to save some time on my worksheet, at the moment I'm manually copying and pasting a row into two ranges.

    I've attached the spreadsheet so you can look at it - Basically on the DAY tab I'm copying the range "B51:AM51" and pasting it into both the MTD and Week tab.

    What i'd like to be able to do is script the VBA so it posts into the table on the next blank row until it hits Row 34 on MTD or Row 10 on the WEEK (these rows have formulas in to calculate a running total).

    While this seems like a really simple task which in reality will only save me a couple of seconds a day however i'd quite like a holiday and these reports need to be bullet proof because the level of excel skills in my work place makes even me look quite good.

    Now I'm getting as far as understanding the procedure behind it but I'm no where near ready to implement it ( I guess you have to Dim the range, paste in next blank row down until row 10/34 then bring up a message box saying full table)

    Ideally I'd like a MSG box to pop up and say "Table full, would you like to clear it?" but I did that part myself so here's the code I've written in case it's wrong
    Code:
    Dim nResult As Long
        nResult = MsgBox( _
        Prompt:="Table is full. Would you like to clear it?", _
     Buttons:=vbYesNo)
     If nResult = vbNo Then
     Exit Sub
     End If
        Range("B3:AM9").ClearContents
    Hurray. 3 days into my books and I can make a Yes/No message box. Baby steps...
    Also, if they say yes to the message box it could loop back to the original part of the macro so that it pastes the data in the first row. But I could probably do that part myself, so feel free to leave it for me as I really need to learn!

    Thanks a lot for reading

    R
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Rathril,

    I believe this will do what you want. Click on the button on the Day sheet to copy the row to the WEEK and MTD sheets. I also corrected your formulas so you do not get the #DIV/0! errors when your summed totals in your Total rows have zeros

    HTH,
    Maud

    Code:
    Public Sub CopyRow()
    Application.ScreenUpdating = False
    '-----------------------------------
    'DECLARE AND SET VARIABLES
    Dim day As Worksheet
    Dim week As Worksheet
    Dim mtd As Worksheet
    Dim Message As String
    Dim weekRow As Integer
    Dim mtdRow As Integer
    Set day = Worksheets("DAY")
    Set week = Worksheets("WEEK")
    Set mtd = Worksheets("MTD")
    weekRow = week.Cells(10, 2) + 3
    mtdRow = mtd.Cells(34, 2) + 3
    '-----------------------------------
    'CHECK IF TABLES ARE FULL
    If weekRow > 9 Then
        Message = "WEEK Table is full. Would you like to clear it?"
        ClearRange Message, week, 9
        Exit Sub
    End If
    If mtdRow > 33 Then
        Message = "MTD Table is full. Would you like to clear it?"
        ClearRange Message, mtd, 33
        Exit Sub
    End If
    '-----------------------------------
    'COPY DAY ROW TO BOTH SHEETS
    day.Activate
    Range("B51:AM51").Copy
    week.Activate
    Range("B" & weekRow).Select
    ActiveSheet.Paste
    mtd.Activate
    Range("B" & mtdRow).Select
    ActiveSheet.Paste
    day.Activate
    '-----------------------------------
    'CLEANUP
    Set day = Nothing
    Set week = Nothing
    Set mtd = Nothing
    '-----------------------------------
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    End Sub
    
        
    Public Sub ClearRange(msg As String, sht As Worksheet, row As Integer)
        nResult = MsgBox( _
        Prompt:=msg, Buttons:=vbYesNo)
        If nResult = vbNo Then Exit Sub
        sht.Range("B3:AM" & row).ClearContents
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2014-10-14 at 23:17.

  3. #3
    Lounger
    Join Date
    Jul 2014
    Posts
    33
    Thanks
    7
    Thanked 3 Times in 3 Posts
    Hi Maudibe. Thanks for your post!

    I tried this script earlier today and think I messed it up when I tried to put it on the end of my other macro... I will try again tomorrow

    R

  4. #4
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    I always thought selections should be avoided where possible?

    'COPY DAY ROW TO BOTH SHEETS
    week.Range("B" & weekRow).Resize(, 50).Value = _
    day.Cells(51, 2).Resize(, 50).Value
    mtd.Range("B" & mtdRow).Resize(, 50).Value = _
    day.Cells(51, 2).Resize(, 50).Value
    '=========
    also Dim nresult

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    YW Rathril. Let me know if you need more help.

    Maud

  6. The Following User Says Thank You to Maudibe For This Useful Post:

    Supershoe (2014-11-01)

  7. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    dg,

    Nice Tweak!

    Maud

  8. #7
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Thanks, I usually try to avoid all selections and copy/pastes where possible

Posting Permissions

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