Tweaking Finding Next Empty Row Based on Column other than 'A'

[WildBurrow]

I found the following code on this website. Works great if I want to paste to the same location each time. However, I want to allow the user to add additional information at a later time and I need the code to find the next available row/cell based upon column H.

    Dim Fname As Variant
    Dim SrcWbk As Workbook
    Dim DestWbk As Workbook
    Dim CopyFromRange As Range
    Dim PasteToRange As Range
   
    Set DestWbk = ThisWorkbook
    
    'Select document
    ChDrive "M:"
    ChDir "M:\Orphan Well Program\Projects"
    Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Select a File")
    If Fname = "False" Then Exit Sub
        Set SrcWbk = Workbooks.Open(Fname)
    
        'Select Well info from document
        On Error Resume Next 'turns off errors
            Set CopyFromRange = Application.InputBox(Prompt:="Select Project Well Names and APIs", Title:="Well Selection", Default:=ActiveCell.Address, Type:=8)
        On Error GoTo 0 'turns on errors
      
        If CopyFromRange Is Nothing Then
            MsgBox "Selection Cancelled", vbOKOnly + vbInformation, "Cancelled"
            GoTo Closewb
            Exit Sub
        Else            
            Set PasteToRange = DestWbk.worksheets("Dashboard").Range("H15") ' First row to paste data, Row 14 contains headers

           'copy from the user selected range
            CopyFromRange.Copy
    
            'paste and transpose values to the user selected range
            PasteToRange.Cells(1).PasteSpecial Paste:=xlPasteValues, Transpose:=False
        End If    
Closewb:
   SrcWbk.Close False
End Sub

I tried rewriting the 'Set PasteToRange' and encountered the following;
Set PasteToRange = DestWbk.worksheets("Dashboard").Range("H14").End(xlDown).Offset(1) 'works if other data in list but not if column empty to the column header
Set PasteToRange = DestWbk.worksheets("Dashboard").Range("H15").End(xlUp).Offset(1) ' works when list completely empty but overwrote previous rows if data added afterwards

I've tried referring to the 'Set PasteToRange' as a range and as cells but I can't get it to work.
I've tried 'Find' but it finds the last row of the table regardless of how may rows between 14:44 are empty.

I'm going a bit bonkers trying to figure out how to speak to it. Any suggestions?

 

[Joe4]

Try this:

Set PasteToRange = DestWbk.worksheets("Dashboard").Cells(Rows.Count, "H").End(xlUp).Offset(1)

 

[WildBurrow]

Try this:

Set PasteToRange = DestWbk.worksheets("Dashboard").Cells(Rows.Count, "H").End(xlUp).Offset(1)

Joe4,

I tried that previously and it pastes the values starting with the last row of the table (e.g. row 44) and then resizes my table.

 

[Joe4]

Joe4,

I tried that previously and it pastes the values starting with the last row of the table (e.g. row 44) and then resizes my table.

You didn't mention that you were dealing with tables! That makes a big difference.
You will probably need to insert a new row at the bottom of your table first.

Take a look here: VBA insert an entire row at the last table row

 

[WildBurrow]

You didn't mention that you were dealing with tables! That makes a big difference.
You will probably need to insert a new row at the bottom of your table first.

Take a look here: VBA insert an entire row at the last table row

Ugh! Sorry about not specifying that it was a table.

Well, your reply has helped me none the less. I converted the table to a range and your recommendation did work. At the moment, I don't believe I need to keep that range as a table ... so I might be good.

Thank you for replying to my question and for pointing me in the right direction.

 

[Joe4]

You are welcome.
Glad you were able to get it to work they way you need!