VBA paste values into last empty row of a table

[lawi227]

On the tab "Instructions" I would like to have a module button. When you click the module button, I would like the following to happen on the tab called "LPA Database Scores Raw Data":

There are two scenarios:

First scenario:
If F5 = "1" on the "Instructions" tab
Copy B126:BO126
Paste as values into the last empty row of a table***

***The table is located in B71:BO120
***I'd like to scan column C to find the next blank row - I picked column C because the other columns may have blanks due to missing data but column C will always have data in it and is the best indicator of which row is blank.

Second scenario:
If F5 = "2" on the "Instructions" tab
Copy B126:BO127
Paste as values into the last empty row of a table***

*** Same rules apply as above

 

[wideboydixon]

Not tested but something like this I think:

Public Sub PasteToLastRow()

Dim instructionsSheet As Worksheet
Dim databaseSheet As Worksheet
Dim lastRow As Long

Set instructionsSheet = Sheets("Instructions")
Set databaseSheet = Sheets("LPA Database Scores Raw Data")

lastRow = databaseSheet.Range("C121").End(xlUp).Row + 1
If lastRow = 121 Then
    ' No more spare rows
    Exit Sub
End If

' Copy the data
databaseSheet.Range("B126:BO12" & CStr(5 + instructionsSheet.Range("F5").Value)).Copy Destination:=databaseSheet.Cells(lastRow, 2)

End Sub

WBD

 

[JackDanIce]

Untested, try:

Sub MoveData()
    
    Dim rng     As rng
    
    With ActiveSheet
        With .Cells(5, 6)
            If .Value = 1 Or .Value = 2 Then Set rng = .Range("B126:B012" & CStr(5 + .Value))
        End With
    End With
    
    If Not rng Is Nothing Then
        With Sheets("LPA Database Scores Raw Data")
            .Cells(.Rows.Count, 3).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
        End With
        Set rng = Nothing
    End With
         
End Sub

 

[wideboydixon]

Reading between the lines, I thought the data to copy was on the database sheet, not the instructions sheet.

WBD

 

[JackDanIce]

It's not clear but I think you're right, in which case, edit #3 to:

Sub MoveData()
    
    Dim rng     As rng
    
    With Sheets("Instructions")
        With .Cells(5, 6)
            If InStr(CStr(.Value), "12") > 0 Then
                Set rng = Sheets("LPA Database Scores Raw Data").Range("B126:B012" & CStr(5 + .Value))
                .Cells(.Rows.Count, 3).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
                Set rng = Nothing
        End With
    End With
    
End Sub

 

[lawi227]

It is. Sorry I did not make that clear.

Reading between the lines, I thought the data to copy was on the database sheet, not the instructions sheet.

WBD

 

[lawi227]

I'm a novice when it comes to VBA and macros. I right click on the button and click "assign macro" correct? I've done this and it isn't working once I test. I may be missing a step.

Should I be posting after this line of code:
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993; background-color: #ffffff}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #fffb00}span.s1 {color: #000000}</style>End Sub
Sub Button8_Click()

 

[JackDanIce]

My code had a slight error, corrected:

Sub Button8_Click()
    Dim rng     As rng
    
    With Sheets("Instructions")
        With .Cells(5, 6)
            If InStr(CStr(.Value), "12") > 0 Then Set rng = Sheets("LPA Database Scores Raw Data").Range("B126:B012" & CStr(5 + .Value))
            .Cells(.Rows.Count, 3).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
        End With
    End With
    
    If Not rng Is Nothing Then Set rng = Nothing
    
End Sub

Replace the code you see with this

 

[lawi227]

Is there anyway to format the values the same as the row above?

My code had a slight error, corrected:

Sub Button8_Click()
    Dim rng     As rng
    
    With Sheets("Instructions")
        With .Cells(5, 6)
            If InStr(CStr(.Value), "12") > 0 Then Set rng = Sheets("LPA Database Scores Raw Data").Range("B126:B012" & CStr(5 + .Value))
            .Cells(.Rows.Count, 3).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
        End With
    End With
    
    If Not rng Is Nothing Then Set rng = Nothing
    
End Sub

Replace the code you see with this

 

[JackDanIce]

Try:

Sub MoveData()
    
    Dim rng     As rng
    
    With Sheets("Instructions")
        With .Cells(5, 6)
            If InStr(CStr(.Value), "12") > 0 Then
                Set rng = Sheets("LPA Database Scores Raw Data").Range("B126:B012" & CStr(5 + .Value))
                With .Cells(.Rows.Count, 3).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count)
                    .Value = rng.Value
                    .Offset(-rng.Rows.Count).Copy
                    .PasteSpecial xlPasteFormats
                    Application.CutCopyMode = False
                End With
                Set rng = Nothing
            End If
        End With
    End With
    
End Sub