Here we go:-
Application.ScreenUpdating = False
Dim lr As Long
Dim lCol As Long
Dim cell As Range
lr = Range("H" & Rows.Count).End(xlUp).Row
lCol = Cells(1, Columns.Count).End(xlToLeft).Column
For Each cell In Range("H2:H" & lr)
If IsDate(cell.Value) Then
Range(Cells(cell.Row, "A"), Cells(cell.Row, lCol)).Copy
Sheet2.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteAll
'Range(Cells(cell.Row, "A"), Cells(cell.Row, lCol)).Delete
Application.CutCopyMode = False
Application.ScreenUpdating = True
As you can see, the above code is slightly different than the other one. It is a "normal" sub routine and needs to be placed in a standard module. To implement the code into your work book:-
- Press Alt + F11 which will take you to the Visual Basic Editor.
- Up the top, select Insert and then select Module from the menu that appears.
- In the big white field that then appears, paste the above code.
Go back to your main sheet (sheet1), create a button somewhere on the sheet. Right click on the button and select "assign macro" from the menu that appears. In the Assign Macro window that appears, select the macro name in the larger field (in this case its named "TransferData") then click OK. Done!
Following is the link to the updated test work book for you to play with:-
In case you were wondering, I usually create my own button as follows:-
- In your main sheet, select the Insert tab up the top and, in the Illustrations Group, select Shapes.
- A window will appear showing many different shapes. I usually select a rectangular one.
- When you have selected a shape, your cursor will turn into a "cross-hair" type cursor. Pick a spot on your sheet and draw the shape with the cursor.
- When you have drawn the shape, you'll notice that a Format tab will light up which will give you many options on how you would like your button to look. Click on the down arrow in the Shape Styles Group and have fun!
Have a play with this function in a blank sheet first just to get used to what you can do or to see how creative you can be. The above steps may seem somewhat drawn out but it actually only takes a few seconds to create a button once you have the routine under control.
When you are satisfied, assign the macro to the button as mentioned further up this post.
You could also use the inbuilt function to create a button by going to the Developer tab, selecting Insert in the Controls group and then selecting the Form Control button to which you can assign the macro to.
As mentioned in my post#9, you'll be able to input data undisturbed until you are ready to transfer data to sheet2 by clicking on the button.
One more thing. If you look at line #15 in the above code (in green font), you'll notice an apostrophe at the begining of the line. If you wish to delete data from sheet1 once it has been transferred to sheet2, then just remove the apostrophe.
I hope that this sorts it all out for you. Let us know how it all works out for you.