VBA Copy Range to new empty cell in new sheet

clazzic

New Member
Joined
May 6, 2013
Messages
26
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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

GCExcel

New Member
Joined
Apr 11, 2013
Messages
48
Hi clazzic,
Your code seems a bit complicated. You don't need to use .Select as it slows down you code.
Try this instead :

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

clazzic

New Member
Joined
May 6, 2013
Messages
26
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

New Member
Joined
Apr 11, 2013
Messages
48
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

New Member
Joined
May 6, 2013
Messages
26
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 Time27/05/201328/05/201329/05/201330/05/201331/05/2013RTD (Care Side)
7:45Teamleader1Teamleader1Teamleader1Teamleader1Teamleader108:00 - 10:00
8:30Teamleader2Teamleader2Teamleader2Teamleader2Teamleader210:00 - 12:00
9:00Teamleader3Teamleader3Teamleader3Teamleader3Teamleader312:00 - 02:00
9:00Teamleader4Teamleader4Teamleader4Teamleader4Teamleader402:00 - 4:00
9:30Teamleader5Teamleader5Teamleader5Teamleader5Teamleader504:00 - 5:30
10:30Teamleader6Teamleader6Teamleader6Teamleader6Teamleader605:30 - 06:30
Start Time27/05/201328/05/201329/05/201330/05/201331/05/2013RTD (Care Side)
7:45Teamleader1Teamleader1Teamleader1Teamleader1Teamleader108:00 - 10:00
8:30Teamleader2Teamleader2Teamleader2Teamleader2Teamleader210:00 - 12:00
9:00Teamleader3Teamleader3Teamleader3Teamleader3Teamleader312:00 - 02:00
9:00Teamleader4Teamleader4Teamleader4Teamleader4Teamleader402:00 - 4:00
9:30Teamleader5Teamleader5Teamleader5Teamleader5Teamleader504:00 - 5:30
10:30Teamleader6Teamleader6Teamleader6Teamleader6Teamleader605: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

New Member
Joined
Apr 11, 2013
Messages
48
Hello again,
Much easier when we see the data... try this code :

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

Watch MrExcel Video

Forum statistics

Threads
1,122,712
Messages
5,597,716
Members
414,166
Latest member
Donerightdata

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top