Have you tried recording a macro to see the code that is generated? The
trick to recording macro's, is that you should try to do all the work
with keystrokes rather than the mouse, so once you figure out that part,
you should be able to get some good code recorded.
Go to = CTRL+G. Type Summary:A1 and then press Enter to place your
cursor in the first cell of the Summary sheet.
CTRL+SHIFT+END will select the Range of non-empty cells in that sheet.
ALT+F, N, will open a New Book, so you can CTRL+V to Paste.
CTRL+End and then down arrow will move your cursor down to the empty
row.
Once you have some code recorded, post it back here if you continue to
have problems getting the code to do exactly what you want, and then we
can help tweak it for you.