Macro to Copy as Many Rows in One Tab as the Number of Rows in the Other Tab

[johnellis1971200]

In the attached Microsoft Excel workbook, I have two tabs.  The first is called "Pay", while the other is called "Job".

You'll notice that there are 4 rows in "Job".  

But, in the "Pay" tab, only two rows of data are completely filled out in each column.

I need to have a macro to run that automatically "creates" the same number of rows in "Pay" as the number of rows in "Job".

I don't care if data among rows in "Pay" is duplicated, as a result of the copying.

Also, in "Pay", I need for the values in column B to increment for each row since that column is a row number identifier.

Finally, I'd like for this macro to be housed within a button to be clicked by the end user.

How do I compose a macro (button) to accomplish both tasks?  Thanks!

https://view.officeapps.live.com/op/view.aspx?src=https%3A%2F%2Ffiledb.experts-exchange.com%2Fincomi... 

[#2 · Accepted solution]

@johnellis1971200 

My knowledge of the topic is limited, but since no one has answered yet, even though it has been read many times, I posted the question in various AIs and found the above suggested solution for you. The proposed solution is untested.

To achieve this in Excel, you can create a VBA macro to copy the required number of rows from the "Job" tab to the "Pay" tab, duplicate the data if needed, and increment the values in column B. Here is a sample VBA code for your specific requirements:

Sub CopyRowsAndIncrement()
    Dim jobSheet As Worksheet
    Dim paySheet As Worksheet
    Dim lastRowJob As Long
    Dim rowCount As Long
    Dim i As Long

    ' Set references to the sheets
    Set jobSheet = ThisWorkbook.Sheets("Job")
    Set paySheet = ThisWorkbook.Sheets("Pay")

    ' Find the last row in the "Job" sheet
    lastRowJob = jobSheet.Cells(jobSheet.Rows.Count, "A").End(xlUp).Row

    ' Clear existing data in "Pay" sheet
    paySheet.Cells.Clear

    ' Copy and duplicate data to "Pay" sheet
    rowCount = 2 ' Start from row 2 in "Pay" sheet
    For i = 1 To lastRowJob
        paySheet.Cells(rowCount, 1).Value = jobSheet.Cells(i, 1).Value
        paySheet.Cells(rowCount, 2).Value = i ' Incrementing values in column B
        rowCount = rowCount + 1
    Next i

    ' Update the number of rows to match the "Job" sheet
    paySheet.Rows(rowCount & ":" & paySheet.Rows.Count).Delete

    ' Inform the user that the operation is complete
    MsgBox "Rows copied and incremented successfully!", vbInformation
End Sub

1. Open your Excel workbook.
2. Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
3. In the VBA editor, right-click on "VBAProject (Your Workbook Name)" in the left pane, select Insert > Module. This will create a new module where you can paste the following code.
4. Copy and paste the following VBA code into the module:

1. Close the VBA editor.
2. Now, you can add a button to your Excel sheet and assign the macro to it.
• Go to the "Developer" tab. If you don't see it, you may need to enable it in Excel options.
• Click on "Insert" and choose the "Button (Form Control)" option.
• Draw the button on your sheet.
• In the "Assign Macro" dialog, select CopyRowsAndIncrement and click "OK".

Now, whenever the user clicks the button, it will run the macro, copying the necessary rows from "Job" to "Pay", incrementing values in column B, and deleting any extra rows in "Pay". Adjust the sheet and cell references in the code if your actual data is in different locations.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.