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

[Postmaster383]

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.

[Logit]

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.

[Postmaster383]

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

[Logit]

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)

[Postmaster383]

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

[Logit]

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.

[Postmaster383]

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.

[Logit]

Ok ... give me a little time.

[Postmaster383]

Ok ... give me a little time.

Thanks, any assistance is greatly apprecaited.

[Logit]

Ok .. this one method. There are plenty of ways to accomplish your goal ... this is only one.

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.