VBA Copy Range to new empty cell in new sheet

[clazzic]

Hi guys,

im new to this forum.

I have a VBA code that copies a range to a new sheet and each time i run the macro, it will copy the range to the next empty cell.

However after copying 3-4 times. it keeps giving me error "Run time Error 1004 - No Cells were found"

This is the code that im using. The line that it highlights when the error comes up is underline. Can anyone help fix this error. ive tried altering the range but it still gives me errors after a few copies.

Sheets("Roster Generator").Select
Range("A1:G8").Select
Selection.Copy
Sheets("Archive").Select
Set emptyCells = Range("A2:A9999").SpecialCells(xlCellTypeBlanks)
Range(emptyCells.Cells(1).Address, emptyCells.Cells(1).Offset(7, 10).Address).Select
ActiveSheet.Paste
Range("A2").Select
Sheets("Roster Generator").Select

 

[GCExcel]

Hi clazzic,
Your code seems a bit complicated. You don't need to use .Select as it slows down you code.
Try this instead :

Sheets("Roster Generator").Range("A1:G8").Copy Sheets("Archive").Range("A60000").End(xlUp)

 

[clazzic]

Hi,

I tried the it. it copies but it doesnt copy on a continous basis to the next empty cell.

And i need it to copy to the new sheet starting. from cell A2.

 

[GCExcel]

Why A2? Are there any values in column A? Do you always copy Range A1:G8 ???
Can you post a sample file... it will be easier to understand what you want to do.

 

[clazzic]

Sheet Roster Generator

So the screenshot is for my first sheet (Roster Geneerator where the data is being copied from, and the bottom is my second sheet2(Archive). Each time i execute the macro. i want it to past it in the Sheet 2 next free cell row +1 (a gap in between each copied table)

I hope this makes it easier.

This data is the copied data in Sheet2 (Archive) the first row is there the title of the roster is. Hence i needed it to start from A2.

DEPARTMENT ROSTER A
Start Time	27/05/2013	28/05/2013	29/05/2013	30/05/2013	31/05/2013	RTD (Care Side)
7:45	Teamleader1	Teamleader1	Teamleader1	Teamleader1	Teamleader1	08:00 - 10:00
8:30	Teamleader2	Teamleader2	Teamleader2	Teamleader2	Teamleader2	10:00 - 12:00
9:00	Teamleader3	Teamleader3	Teamleader3	Teamleader3	Teamleader3	12:00 - 02:00
9:00	Teamleader4	Teamleader4	Teamleader4	Teamleader4	Teamleader4	02:00 - 4:00
9:30	Teamleader5	Teamleader5	Teamleader5	Teamleader5	Teamleader5	04:00 - 5:30
10:30	Teamleader6	Teamleader6	Teamleader6	Teamleader6	Teamleader6	05:30 - 06:30
Start Time	27/05/2013	28/05/2013	29/05/2013	30/05/2013	31/05/2013	RTD (Care Side)
7:45	Teamleader1	Teamleader1	Teamleader1	Teamleader1	Teamleader1	08:00 - 10:00
8:30	Teamleader2	Teamleader2	Teamleader2	Teamleader2	Teamleader2	10:00 - 12:00
9:00	Teamleader3	Teamleader3	Teamleader3	Teamleader3	Teamleader3	12:00 - 02:00
9:00	Teamleader4	Teamleader4	Teamleader4	Teamleader4	Teamleader4	02:00 - 4:00
9:30	Teamleader5	Teamleader5	Teamleader5	Teamleader5	Teamleader5	04:00 - 5:30
10:30	Teamleader6	Teamleader6	Teamleader6	Teamleader6	Teamleader6	05:30 - 06:30

<COLGROUP><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3693" width=101><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3474" span=5 width=95><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4498" width=123><TBODY>
</TBODY>

 

[GCExcel]

Hello again,
Much easier when we see the data... try this code :

Sheets("Roster Generator").Range("A2:G8").copy Sheets("Archive").Range("A60000").End(xlUp).Offset(2, 0)