VBA Copy data

[staticbob]

HI,

I have 2 pages in a spreadsheet. I would like to automate copying data from one to another.

Firstly, I need some code to add to an existing button that will copy the task list from A6:Azzz (could be A6:A20 could be A6:A4544) and paste this onto a new page in A2:Azzz

I then need some code that will copy the report date and actual %ages from the progress report to this new page. This will be done weekly so evetually this new sheet will show where we were upto on each report date. This data could be pasted into the next available column, if the reports are done in date order. Sometimes these will be done retrospectively so I need to ensure that it will paste into the correct place.

Thanks in advance.

Derby Uni PR.xls
	A	B	C	D	E	F
1	SiteName		MarkeatonCampus-UniversiyofDerby
2	ProjectNumber RelatedProgrammeRevision		E32012 ConstructionProgrammeRev1st
3			ReportDate	Average	Actual
4			12/12/2003	5%	11%
5	Activity	StartDate	FinishDate	Schedule	Actual	Slippage (Days)
6	ErectHoardingtoSiteBoundary	24/11/200308:00	26/11/200317:00	100%	100%	0
7	Locate&MarkExistingServices	24/11/200308:00	26/11/200317:00	100%	100%	0
8	ErectionofExternalScaffold	08/12/200308:00	19/12/200317:00	50%	100%	-5
9	SetupSiteCompound	05/01/200408:00	07/01/200417:00	0%	100%	-3
10	AsbestosRemovaltoRearBays	20/11/200308:00	05/12/200317:00	100%	100%	0
11	AsbestosRemovaltoFrontBays	08/12/200308:00	28/01/200417:00	13%	100%	-33
12	ExcavateforNewDrainage	08/12/200308:00	09/01/200417:00	20%	100%	-20
13	LayPipes&ConnecttoExisting	07/01/200408:00	14/01/200417:00	0%	0%	0
14	Backfill&MakeGoodGround	05/01/200408:00	23/01/200417:00	0%	0%	0
15	StripOutRedundantServices&Sanitaryware	08/12/200308:00	19/12/200317:00	50%	100%	-5
Progress Report

 

[staticbob]

Thats still only copying 20 rows. A6:A26 ??
Strange . .

 

[jimboy]

Select cell A65536 and hold down Control and the Up Arrow, what cell is selected?

 

[staticbob]

Cell A102 in this case. Works fine on all task lists, regardless of size.

The VBA code always selects this smaller range tho.

very strange . . .

 

[jimboy]

What row is returned when you run this?;

Sub getlastrow()
MsgBox ("Last row in col. A is " & Range("A65536").End(xlUp).Row)
End Sub

 

[staticbob]

That works - row 102 ???

 

[jimboy]

and you did run it on the progress report sheet?

try this;

Sub firstcopy()
Dim mylastrow As Integer
mylastrow = Range("A65536").End(xlUp).Row
Sheets("Progress Report").Range("A6:E" & mylastrow).Copy Sheets("As Built Data").Range("A2")
Sheets("As Built Data").Columns("B:D").Delete

End Sub

 

[staticbob]

Sorry Jimboy, I was running this from a command button on another sheet and assumed it would still work. It was taking the last row from the sheet containing the button.

On the 2nd part of the code, it copies the data OK to the As Built Page, but I get d runtime error 1004 on . .

Sheets("As Built data").Range(Cells(2, mycolumn), Cells(5000, mycolumn)).NumberFormat = "0.00%"

Application or Object-defined error

It pastes the data OK, just doesn't format to %

Any ideas ?

 

[jimboy]

when it debugs, if you hover the mouse over mycolumn, what does it say?

 

[staticbob]

Sorry for the delay Jim,

The 2 mycolumn fields show as 2 and 3 respecively when it debugs

Also, its not pasting in all the values, its missing a few at random ? It should paste data into A2:A191, A53,54,55,60,66 and 87 are empty ? I have checked the source data and formats are exactly the same throughout.


Cheers