Copy data by autofiltering and pasting to last empty row in another sheet

[JasonWilliam]

I have a sheet named BOM that I copy and paste the data from into a sheet named BOM Data. At the moment I have to physically highlight the data in order to select the range. I would like to just click the button I created and have it copy the Range (If it has a value in it) to the next empty row in my BOM Data sheet. This is my code I have at the moment. Any help would be appreciated. BTW the range is A4:H104...


Private Sub CommandButton1_Click()


Dim xScreenUpdating As Boolean
Dim xPasteSht As Worksheet
Dim xRg As Range
Dim xTxt As String

On Error Resume Next
xTxt = ActiveWindow.RangeSelection.Address

Set xRg = Application.InputBox("Please select a range:", "Transfer Tool", xTxt, , , , , 8)
If xRg Is Nothing Then Exit Sub

Set xPasteSht = Worksheets("BOM Data")
xScreenUpdating = Application.ScreenUpdating

Application.ScreenUpdating = False
xRg.Copy
xPasteSht.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = xScreenUpdating

End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

Private Sub CommandButton1_Click()
   With Range("A4:H" & Range("A" & Rows.Count).End(xlUp).Row)
      Sheets("BOM Data").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(.Rows.Count, .Columns.Count).Value = .Value
   End With
End Sub

 

[JasonWilliam]

That works great thank you!

 

[Fluff]

You're welcome & thanks for the feedback.