VBA - Paste in next empty row, multiple copy selections

[Nordicrx8]

Hi all!

I'm using the below code to copy content from one sheet to another, in the next empty row. It's working perfectly for one copy selection, but I need to make another copy selection and paste it at the SAME ROW starting point, but in a different column.

For example, it copies B2-M16, and drops it in the "Main" Tab starting in row 50, which is the next empty row. I then need it to copy B17:M37, and paste it in column FJ, starting in the same empty row as before, row 50.

I'm not sure how to do this, and google isn't being helpful. But it may be my search terms. Any help on this would be appreciated!

thanks all!

Private Sub Update_Master_Click()

Sheets("Sch_Pricing").Range("B2:M16").Copy

Dim lastrow As Long
lastrow = Range("A65536").End(xlUp).Row

Sheets("Main").Activate
Cells(lastrow + 1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

 

[GWteB]

First determine the target row number on the target sheet and store it in a variable.
Then use that variable each time a paste action is to be performed.
Note the use of the With statement. That way switching sheets can be avoided.

Private Sub Update_Master_Click()

    With Sheets("Main")

        Dim FirstEmptyRow As Long
        FirstEmptyRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row

        Sheets("Sch_Pricing").Range("B2:M16").Copy
        .Cells(FirstEmptyRow, "A").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

        Sheets("Sch_Pricing").Range("B17:M37").Copy
        .Cells(FirstEmptyRow, "FJ").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

        Excel.Application.CutCopyMode = False
    End With
End Sub

 

[Nordicrx8]

First determine the target row number on the target sheet and store it in a variable.
Then use that variable each time a paste action is to be performed.
Note the use of the With statement. That way switching sheets can be avoided.

Private Sub Update_Master_Click()

    With Sheets("Main")

        Dim FirstEmptyRow As Long
        FirstEmptyRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row

        Sheets("Sch_Pricing").Range("B2:M16").Copy
        .Cells(FirstEmptyRow, "A").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

        Sheets("Sch_Pricing").Range("B17:M37").Copy
        .Cells(FirstEmptyRow, "FJ").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

        Excel.Application.CutCopyMode = False
    End With
End Sub

Ah, perfect! so smart - thank you so much! It worked beautifully I appreciate you!

 

[GWteB]

You are welcome and thanks for the feedback

 

[Nordicrx8]

First determine the target row number on the target sheet and store it in a variable.
Then use that variable each time a paste action is to be performed.
Note the use of the With statement. That way switching sheets can be avoided.

Private Sub Update_Master_Click()

    With Sheets("Main")

        Dim FirstEmptyRow As Long
        FirstEmptyRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row

        Sheets("Sch_Pricing").Range("B2:M16").Copy
        .Cells(FirstEmptyRow, "A").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

        Sheets("Sch_Pricing").Range("B17:M37").Copy
        .Cells(FirstEmptyRow, "FJ").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

        Excel.Application.CutCopyMode = False
    End With
End Sub

Hey There!

I need to add a feature to this workbook, and I'm scratching my head on how to do it - was hoping you could help!

Instead of the code searching for the next available line, I need it to look at the Sch_Pricing tab in cell B1, and search in the Main tab (in column A) for the FIRST match, and paste over the data that's there, with the same code/data as above.

Any help you could provide would be really appreciated!

Thanks so much!
Chris

 

[GWteB]

I'm afraid you need to elaborate.

Instead of the code searching for the next available line,

The purpose of the empty row lookup was to determine the destination when copying from Pricing sheet to Main sheet. If that's not needed anymore what would be the destination?

This part of your current query:

I need it to look at the Sch_Pricing tab in cell B1, and search in the Main tab (in column A) for the FIRST match

... is clear enough ...

Regarding this part:

and paste over the data that's there, with the same code/data as above.

which data (just one cell or the entire row??) needs to be pasted and what should be the destination sheet and the destination range?

 

[Nordicrx8]

I'm afraid you need to elaborate.

The purpose of the empty row lookup was to determine the destination when copying from Pricing sheet to Main sheet. If that's not needed anymore what would be the destination?

Basically instead of adding a new record at the bottom (next empty row) I'm looking to UPDATE an existing record. So it needs to find the record number located on the Sch_Pricing tab in cell B1, search for a match in the Main tab in col A for a match, then run the rest of the code.

This part of your current query:

... is clear enough ...

Regarding this part:

which data (just one cell or the entire row??) needs to be pasted and what should be the destination sheet and the destination range?

It needs to past all of the same data - I'd imagine the rest of the code says the same, it's just the initial code of finding where to paste the data that needs to change.

Thanks again for your help!

 

[Nordicrx8]

I'm afraid you need to elaborate.

The purpose of the empty row lookup was to determine the destination when copying from Pricing sheet to Main sheet. If that's not needed anymore what would be the destination?

This part of your current query:

... is clear enough ...

Regarding this part:

which data (just one cell or the entire row??) needs to be pasted and what should be the destination sheet and the destination range?

I know you're probably super busy, but if you have a quick moment I'd really appreciate it!

 

[GWteB]

You really need to clarify ....

Instead of the code searching for the next available line, I need it to look at the Sch_Pricing tab in cell B1,
and search in the Main tab (in column A) for the FIRST match, and paste over the data that's there, with the same code/data as above.

Basically instead of adding a new record at the bottom (next empty row) I'm looking to UPDATE an existing record.
So it needs to find the record number located on the Sch_Pricing tab in cell B1,
search for a match in the Main tab in col A for a match, then run the rest of the code.

It needs to past all of the same data - I'd imagine the rest of the code says the same,
it's just the initial code of finding where to paste the data that needs to change.

You're assuming that "the rest of the code" (as per post #2) fits your current query, but it doesn't. Your query is different so the code will be different.
As I have no insight into your data and your explanation isn't sufficient I'm not able to help you.