copy variable range from one sheet to top of another, rather than bottom.

[y0rk1e72]

I'm using the following code, this works great as it picks data up from the Cmonth sheet and makes a copy of this on the archive sheet.
It currently copies rows with an entry in and places it on the last blank row of the archive sheet.

Normally this would be sorted to put the newest copied data to the top.
However i'm being told that there is a need to run this once a month for a year.

The range to be copied each month could be from 25 to 45 rows. That would mean to have the newest copied data at the top would mean potentially sorting over 500 rows.

Is there a way to modify the macro below to place the copied range at the top thus removing the need to sort each month.

I've searched and tried to mod for two days now with no success. If anyone can point me in the wright direction please do!

Please Login or Register  to view this content.

Many thanks for taking the time to read the above.

[g8r777]

Can you attach a sample workbook? What you will need to do is calculate the number of rows of data in Cmonth, insert that many rows at the top of Archive which whould push all data down, and then copy the rows from Cmonth to Archive.

You can use code similar to this:

Please Login or Register  to view this content.

on the Archive sheet where X equals the number of rows of data in Cmonth that you want to copy over.

[y0rk1e72]

Attached is a sample file.

To test i've been manually changing the date in cell D10 on the Cmonth sheet.
Click on Archive to run the macro.

Many thanks

[y0rk1e72]

Tried calculating the number of rows which works but when i add this into the macro above i get

Object doesn't support this property or method

any ideas?

[y0rk1e72]

I've got the following macros in the attached file

Sub Count_rows_and_insert()

Dim i As Integer
endrow = Cells(ActiveSheet.UsedRange.Rows.Count + 1, ActiveCell.Column).End(xlUp).Row
Sheets("sheet2").Select
For i = 1 To endrow
Rows("2:2").Select
Selection.Insert shift:=xlDown, copyorigin:=xlFormatFromLeftOrAbove
Next i
End Sub 

This worked once but now inserts one line when run again

Sub Select_Copy_Paste()
With ActiveSheet
.Range("C7", .Cells.SpecialCells(xlCellTypeLastCell)).Select
End With
Selection.Copy
Sheets("Sheet2").Select
Range("C3").Select
ActiveSheet.Paste
Columns.AutoFit
End Sub 

The one above again worked once but now only copies 5 rows.

What i'd like to be able to do is combine bothe macros to do the following
* count the number of used rows on sheet1
* insert the number of rows on sheet2
* select the used range on sheet1 and copy it to sheet2 where the first line was inserted.

Can anyone please point me in the right direction/help.

Many thanks