Search worksheet for next blank cell in column B and copy data in row from another workshe

My head is all spins. have tried various methods but just cant get what im looking for. Would greatly appreciate if any help rendered.

I have a workbook with 2 worksheet. "Authorization" sheet is the one in which data is entered by a user, it has 2 buttons that run a macro, "Accept" and "Reject". The other worksheet is a kind of database called "Header" which has a row 1 with the headings and in column A are predefined serial numbers "P001-P008". The purpose is to maintain statistics.

What I want to do is when the user clicks the Accept button on Authorization sheet, it should search Column B range B4:B11 in Header sheet (since column A already had data which must not change), for next empty cell and then start copying the data from different cells in Authorization to the relevant columns in Header sheet as per row headings.

E.G, search Header sheet for next empty cell between column B4:b:11, select that row and copy data from Authorization sheet cell B8 to Header sheet B column in that empty row detected, copy Authorization sheet C8 to header sheet C in the same empty row detected.

I don't know if I make sense or if its any possible even. but would appreciate help as mentioned earlier.

i'm attaching the structure of the workbook and the code im working on. let me mention the codes is a jumble of several stuff that I tried but failed to get what I want. that's while you will find much of it commented. but NONE worked . please help

Dim ws As Worksheet
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    
    Dim cwsh As Worksheet
    Dim r As Long
    Dim c As Long
    Dim strSheetName As String
    
  
    Set ws = Sheets("Authorization")
    Set ws1 = Sheets("StaffName")
  
  
    strSheetName = ws.Range("F5")

        If Not wsExists(strSheetName) Then
            Worksheets("Header").Copy after:=Sheets("Header")
            Worksheets("Header (2)").Name = strSheetName
            Set cwsh = ActiveWorkbook.Worksheets(strSheetName)
            'cwsh.Name = strSheetName   'Add(After:=Worksheets(Worksheets.Count))
            'Worksheets("Header").Cells.Copy
            'With Worksheets(strSheetName)
             '   .Rows(1).PasteSpecial xlFormats
              '  .Rows(1).PasteSpecial xlValues
               ' .Columns(1).SpecialCells(xlBlanks).EntireRow.Delete
            'End With
            'Worksheets("Header").Rows(14).Copy Worksheets(strSheetName).Rows(14)
            'Worksheets("Header").Columns("A").Copy Worksheets(strSheetName).Columns("A")
            Application.CutCopyMode = False
            Else
            Set cwsh = ActiveWorkbook.Worksheets(strSheetName)
            End If
            
            
            For Each cell In cwsh.Range("B4:B11")
            If IsEmpty(cell) = True Then
            cwsh.Cells(r, 2) = ws.Range("B8")
            
            
            'With Worksheets(strSheetName)
            'Row = 4
            'Do While Cells(Row, 2) <> ""
            'Cells(Row, 2).Activate
            'Row = Row + 1
            'Loop
            
            
            'cwsh.Range("B4").Select
            'Do Until ActiveCell.Row = 11
            'Selection.End
            
            
            'r = cwsh.Range("B4:B11" & Rows.Count).End(xlUp).Row + 1
            'r = cwsh.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            'cwsh.Cells(r, 2) = ws.Range("B8")
            'r = cwsh.Range("B11").End(xlUp).Offset(1, 0).Select
            'ActiveCell.FormulaR1C1 = ws.Range("B8")
            'r = cwsh.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Try this for the Accept button. There was no correlation for the "status" field

Sub Accept()
Dim ws1 As Worksheet:   Set ws1 = Sheets("Authorization")
Dim ws2 As Worksheet:   Set ws2 = Sheets("Header")
Dim lastrow As Long

Application.ScreenUpdating = False

lastrow = ws2.Range("B14").End(xlUp).Offset(1, 0).Row

If lastrow > 11 Then
    MsgBox ("There is no more room left in Range B4:B11 on the Header sheet")
    Exit Sub
End If

ws1.Range("B8:D8").Copy
ws2.Range("B" & lastrow).PasteSpecial (xlPasteValues)

ws1.Range("C20").Copy
ws2.Range("F" & lastrow).PasteSpecial (xlPasteValues)

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

thanx a ton stnkynts. that worked like a charm. BUT one problem. As you may have noticed there are 4 row of records on the Authorization sheet and hence the user can input upto 4 records at a time. so when I edited the ws1 range as:

 Dim ws1 As Worksheet:   Set ws1 = Sheets("Authorization")
Dim ws2 As Worksheet:   Set ws2 = Sheets("Header")
Dim lastrow As Long

Application.ScreenUpdating = False

lastrow = ws2.Range("B14").End(xlUp).Offset(1, 0).Row

If lastrow > 11 Then
    MsgBox ("There is no more room left in Range B4:B11 on the Header sheet")
    Exit Sub
End If

ws1.Range("B8:F11").Copy
ws2.Range("B" & lastrow).PasteSpecial (xlPasteValues)

ws1.Range("C20").Copy
ws2.Range("F" & lastrow).PasteSpecial (xlPasteValues)

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

it is copying all the range to the sheet but the Approver (C20 of Authorization) gets copied only in the first record that gets updated in the Header sheet. What needs to be done is say e.g there are 3 records in Authorization then, which get updated in the Header sheet, then all those 3 records should have the same name of the approver copied as well since it would be the same approver approving all 3.

My apologies. The Column heading "Status" is instead "Agreed By". And correct cell C20 of Authorization is to be filled in Approver column of Header.

Also, the authorization form may have upto 4 recordz at a time.

Thanx a zil for all ur attention

Sub Accept()
Dim ws1 As Worksheet:   Set ws1 = Sheets("Authorization")
Dim ws2 As Worksheet:   Set ws2 = Sheets("Header")
Dim c As Range
Dim lastrow As Long

Application.ScreenUpdating = False

For Each c In ws1.Range("B8:B11").SpecialCells(xlCellTypeConstants)

lastrow = ws2.Range("B14").End(xlUp).Offset(1, 0).Row

If lastrow > 11 Then
    MsgBox ("There is no more room left in Range B4:B11 on the Header sheet")
    Exit Sub
End If

c.Resize(1, 4).Copy
ws2.Range("B" & lastrow).PasteSpecial (xlPasteValues)

ws1.Range("C20").Copy
ws2.Range("F" & lastrow).PasteSpecial (xlPasteValues)

Next c

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

This is great. I can only thank less. no intention of being nasty but just one last question. if its possible.

at the end the process a messagebox could pop that displays the pre-feed reference number in column A of the Header sheet corresponding to only the new records that were updated.

Thanks. I figured it out. the intersect method. Again many thanks for all you valuable help.