Inserting new sheet and pasting selected data cells



I am making a macro to transfer weekly data (always 8 columns) and varing number of rows. 

I want to paste the weekly data on the "Data Input" sheet, run the macro which I want to do the following; 

1) Bring up a box that the user can write the name for the weekly data.

2) Have a new sheet be created at the end of the sheet list (to the most right) named what the user entered in point 1. 

3) Cut and paste the data from the "Data Input" sheet to the new sheet.

This is my script so far but I can't get the point 3 to work. 

Sub DataMovement()
'Input box for experiment name
Dim myValue As Variant
    myValue = InputBox("Experiment Name")

'Adding new sheet to the end using experiment name input
Sheets.Add(After:=Sheets(Sheets.Count)).Name = myValue

'Cutting and pasting data from input sheet onto the new experiment sheet

    Sheets("Data Input").Range("A1:H1", Range("A1:H1").End(xlDown)).Copy Destination:=Sheets("myValue").Range("A1")

End Sub



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.


Answer the Question

You must create an account to use the forum. Create an Account or Login