Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: Macro for copying and pasting data to another area without overwriting last rows

  1. #1
    Seeker Postmaster383's Avatar
    Join Date
    Jul 2021
    Posts
    9
    Articles
    0
    Excel Version
    2013

    Macro for copying and pasting data to another area without overwriting last rows



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I'm on a ship and I have a spreadsheet which I update daily at Noon, mainly for fuel and gas consumption. I have a hidden row which mirrors various data around the spreadsheet which is generated from various formula around the spreadsheet, this data is cell range Q56:Y56.

    I want to click the "Noon" button and for the data to copy into the next available row in the table range Q:Y rows 4 to 53. I don't want it overwriting previous data but copying into the next blank row. So the data being copied and pasted is within the same worksheet.

    The various data I want to copy is in cells Q56:Y56 (not in the image below) and I want to press the NOON button and for it to paste into the next available row in my table (rather than manually entering it each day). So for Jul 6, it would paste into Row 20 columns Q:Y. If I was to press multiple times it would keep filling up the next available row to a maximum of Row 53 columns Q to Y. I don't think it's possible to record a macro to simulate this so will need some extra code.
    Thanks in advance for any help given.
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Capture.jpg 
Views:	17 
Size:	111.3 KB 
ID:	10557  

  2. #2
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    323
    Articles
    0
    Excel Version
    2007
    A picture doesn't provide the best information to create a response to your request. You can click the GO ADVANCED button in the REPLY window (lower right) and attach a copy of your workbook.

  3. #3
    Seeker Postmaster383's Avatar
    Join Date
    Jul 2021
    Posts
    9
    Articles
    0
    Excel Version
    2013
    Quote Originally Posted by Logit View Post
    A picture doesn't provide the best information to create a response to your request. You can click the GO ADVANCED button in the REPLY window (lower right) and attach a copy of your workbook.
    Hi, thanks for replying. I've attached a copy of this workbook. Every day the spreadsheet is copied and renamed and updated for every voyage.
    Thanks
    Attached Files Attached Files

  4. #4
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    323
    Articles
    0
    Excel Version
    2007
    I'm having great difficulty determining which cells the following information is located. You have a very busy table on the left side.

    Can you specify which cell populates the following readings ?


    Reading Type Cx Date / Time Hrs Obs Miles Enter RPM Enter Slip (%) Enter Speed (kn) Enter HFO Cons (MT) Enter Noon HFO ROB (MT) Enter LSDO Cons (MT) Enter Noon LSDO ROB (MT) Enter LNG Cons (MT)

  5. #5
    Seeker Postmaster383's Avatar
    Join Date
    Jul 2021
    Posts
    9
    Articles
    0
    Excel Version
    2013
    Quote Originally Posted by Logit View Post
    I'm having great difficulty determining which cells the following information is located. You have a very busy table on the left side.

    Can you specify which cell populates the following readings ?


    Reading Type Cx Date / Time Hrs Obs Miles Enter RPM Enter Slip (%) Enter Speed (kn) Enter HFO Cons (MT) Enter Noon HFO ROB (MT) Enter LSDO Cons (MT) Enter Noon LSDO ROB (MT) Enter LNG Cons (MT)
    Hello,

    Ignore everything on the left hand side. If you go down to row 56 there are a set of values. From columns Q to Y. I want these copied into the same columns in the table directly above. This is everything from Obs Miles to Enter LNG Cons (MT). The spreadsheet is being copied and renamed new every day so as time progresses the table will fill up. The idea is, instead of manually typing these figures in the table each day, pressing the Noon button to simply copy and paste the daily values in row 56 to the next available row in the table (without deleting any other data). So in the example I gave you, the numbers should paste into row 20 in same columns. If I pressed Noon a 2nd time it would go into row 21 and so on.
    302 51.18 rpm 3.8% 12.08 0 0 0 1906.07 52.48

  6. #6
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    323
    Articles
    0
    Excel Version
    2007
    Ok ... it's beginning to make more sense.

    Where does the data for Cols M:P come from ? Seems you would want to automate the entry of those cols as well.

  7. #7
    Seeker Postmaster383's Avatar
    Join Date
    Jul 2021
    Posts
    9
    Articles
    0
    Excel Version
    2013
    Quote Originally Posted by Logit View Post
    Ok ... it's beginning to make more sense.

    Where does the data for Cols M:P come from ? Seems you would want to automate the entry of those cols as well.
    Those can't really be automated as we don't always take readings at Noon. For example when the voyage ends it's called End of Passage (EOP) so would manually select. Add the time and date manually. As the clocks change this is represented by -1, 0, or 1. The No of hours always has formula so basically it's only Q to Y. Also, for now, we don't have any HFO (heavyfuel oil) on board so the spreadsheet was modified but there's a chance we may get some in the future. So for now those will always be zero.

  8. #8
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    323
    Articles
    0
    Excel Version
    2007
    Ok ... give me a little time.

  9. #9
    Seeker Postmaster383's Avatar
    Join Date
    Jul 2021
    Posts
    9
    Articles
    0
    Excel Version
    2013

    Talking

    Quote Originally Posted by Logit View Post
    Ok ... give me a little time.
    Thanks, any assistance is greatly apprecaited.

  10. #10
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    323
    Articles
    0
    Excel Version
    2007
    Ok .. this one method. There are plenty of ways to accomplish your goal ... this is only one.

    Code:
    Sub CopyOwnTab()Application.ScreenUpdating = False
    Dim i As Long
    Dim Lastrow As Long
    Dim ws As Worksheet
    Dim ans As String
    
    
    Set ws = ActiveSheet
    
    
    On Error GoTo M
    
    
    Lastrow = Sheets("NOON Figs").Cells(Rows.Count, "Q").End(xlUp).Row
    
    
        For i = 2 To Lastrow
        ans = Sheets("NOON Figs").Cells(i, 17)
            Sheets("NOON Figs").Cells(i, 17).Resize(, 9).Copy
                
                For Each cell In ws.Columns(17).Cells
                    If IsEmpty(cell) = True Then cell.Select: Exit For
                Next cell
        Next
        
            ActiveCell.PasteSpecial xlValues
            
    Application.ScreenUpdating = True
    Exit Sub
    
    
    M:
    MsgBox "No such sheet as  " & ans & " exist"
    Application.ScreenUpdating = True
    
    
    End Sub
    With the above, I unmerged cell M54 and moved the title "Average / Totals" into cell N54. This is required for the macro to function correctly. There must not be anything entered in the M COLUMN in or below M54.

Page 1 of 2 1 2 LastLast

Posting Permissions

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