VBA to transfer specific data from one sheet to another.

[dwmlr]

Hello again ladies and gentlemen.

I've come up with some VBA with help from YouTube, but it's not grabbing everything I want it to grab and placing it on the other sheet.

I'm running Office 2016 on my Windows 7 computer.

I'm attempting to grab data from sheet AdhocSheet cells M2, M5 and B9:B17
It's supposed to place the data on the sheet Report into cells C16, D16 and H16.

M2 is to go to cell H16.
M5 is to go to cell C16.
B9 is to go to cell D16.

The issue I'm having is it will only return 1 row of data to my report sheet. If I only input 1 row of data into my Adhoc sheet, it returns that fine. If I have 2 or more lines, it will only place the first row into my Report sheet. No errors, but will only bring over that single bit.

I don't need it to bring over cells M2 and M5 from the Adhoc sheet to each line on the receiving sheet, but I do need it to bring over whatever is put into cells B9:B17. There may only be one line to bring over at a time or may be 9. It's always different.

I've tried to change B9 into B9:B17 in the Route=Range("B9"). Everything else I have tried has resulted in runtime errors.

My code is thus:

Private Sub CommandButton1_Click()
Dim Route As String, DriverID As Integer, Dated As Date
Worksheets("AdhocSheet").Select
Route = Range("B9")
DriverID = Range("M2")
Dated = Range("M5")
Worksheets("Report").Select
Worksheets("Report").Range("D15").Select
If Worksheets("Report").Range("D15").Offset(1, 0) <> "" Then
Worksheets("Report").Range("D15,D16,D17,D18,D19,D20,D21").End(xlDown).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = Route
ActiveCell.Offset(0, -1).Select
ActiveCell.Value = Dated
ActiveCell.Offset(0, 5).Select
ActiveCell.Value = DriverID
Worksheets("AdhocSheet").Select
Worksheets("Adhocsheet").Range("B9").Select

End Sub


Private Sub CommandButton2_Click()
Worksheets("AdhocSheet").Range("B9:C17").ClearContents


End Sub


Function PrintActiveSheet()
ActiveSheet.PrintOut
End Function

I'll place the file on my OneDrive in case it'll make it easier for you to understand what I have and what I'm trying to do.

https://1drv.ms/x/s!AnZn4yaJBJpo5lfyJeX_hcg8doJo

Thank you all so much for your help and time.

Doug

 

[dwmlr]

OneDrive apparently has taken away the 3 buttons I that were on my adhoc sheet.There were 3. 1st was to transfer the data to the report sheet, 2nd was to clear the adhoc sheet, and the 3rd button was to print the adhoc sheet.

 

[dwmlr]

bump