Copy Pasting data from one excel workbook to another using vba

Hi Guys,

I'm a newbie at vba. I want to write a vba script that copies 73 cells from a specific row in an excel sheet and pastes them in a single column in 73 rows in a new document. It then goes back to the old document and copies 73 cells from the next row and pastes them in the new document.

I want to do the following things using the macro

1. Open a old.xls document
2. Open a new new.xls workbook
3. Copy a range of data from (D9:BX9) //73 cells from old.xls
4. Paste the copied data from (D2:D74) in new.xls
5. Go back to data.xls document
6. Copy the range from (D10:BX10)
7. Go back to new.xls (opened earlier) and pastes it in (D75:148)
8. Continues in loop until it reaches an empty cell in data.xls
9. Saves the new.xls and exits

I'm trying to use a script I found online and modified a bit. I now just can't make it work. I would be much obliged if someone could help me with this.


Script

Please Login or Register  to view this content.

The following code does the what I wanted to do, but takes a hell lot of time due to working cell by cell.

Can someone help me in optimizing this baby,so that I can pick ranges instead of cell by cell?

Please Login or Register  to view this content.