Selected Answer
Your syntax for the third action is illogical and therefore wrong.
Sheets("Data Input").Range("A1:H1", Range("A1:H1").End(xlDown)).Copy
TBelow is correct, schematic syntax for setting a range.
Sheets("Data Input").Range("A1:H1")
As you see, the range is defined by pointing at its first and last cells. Your construct would make better sense if it would follow this concept.
Sheets("Data Input").Range("A1"), Range("H1")).Copy
However, that wouldn't work either because in this range the last cell is H1, and that isn't what you have in mind. With your plan you are coming very near to the boundary of what you can do with syntax like Range("H1") to define a cell. This looks simple but actually is very cumbersome. What you want looks more complicated but ends up being easier, in short, the default way by which VBA defines a cell:
Cells(1, 8)
This addresses H1, that is row 1, column 8. Observe that not a range is specified but one member of the Cells collection, which comprises of all cells in a worksheet. The beauty of this sytem is that you can calculate both values. You need this capability if you want to find the last used row.
Sheets("Data Input").Cells(Sheets("Data Input").Rows.Count, "A").End(xlUp))
This formula repeats the name of the sheet twice. Once it specified the Cells collection of that sheet. In the second instance it counts the number of rows. Therefore one would use a With statement which permits the replacement of each instance with a leading period.
With Sheets("Data Input")
.Cells(.Rows.Count, "A").End(xlUp))
End With
Of course, you now recognise the cell being specified:- Cells(Rows.Count, "A")
Here Rows.Count is the number of the last row in the sheet. I used "A" here to specify the column. That is also correct syntax but normally one would use a numeric 1. The instruction is to take the last cell in column A and find the first used cell above it. That's why it must be xlUp, not xlDown.
Of course, the last used cell in column A is a cell lol: Therefore it can be the last cell in the range you want to copy from. Let's start simple.
With Sheets("Data Input")
.Range(.Cells(1, "A"), .Cells(1, "H"))
End With
This specifies the Range("A1:H1"). Observe that there are 3 leading periods, each one linking to the Sheet. BTW, I always recommend the use of Worksheets rather than Sheets. The Sheets collection includes sheets which aren't worksheets. Your workbook probably doesn't have such sheets. So it makes no difference. But if it makes no difference, why use the wrong collection?
Now you could replace the last cell of the defined range with the last cell in column H.
With Sheets("Data Input")
Debug.Print .Range(.Cells(1, "A"), .Cells(.Rows.Count, "H").End(xlUp))).Address
' Prints the range's address to the Immediate window
End With
Can you spot the difference between the above syntax and the one below?
With Sheets("Data Input")
.Range(.Cells(1, "A"), .Cells(.Cells(.Rows.Count, "A").End(xlUp).Row, "H"))
End With
Here is a hint. Cells(1, "A") or Cells(1, 1) specifies a cell. However, a "cell" is a "range" and a range has a "Row" property. Therefore Cells(1, 1).Row returns the row number of Range ("A1") which, of course, is 1. But in .Cells(.Rows.Count, "A").End(xlUp).Row the row number is calculated dynamically.
I think you will be able to take it from here. Good luck.
