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

 

[jimboy]

Hi,

Not sure if you want a new sheet adding or you have another sheet you want to paste to.

This will paste to sheet2;

Sub test()
'copy to sheet2
Sheets("Progress Report").Range("A6:A" & Range("A65536").End(xlUp).Row).EntireRow.Copy Sheets("Sheet2").Range("A2")

End Sub

Where do you want the report date (C4) and actual % (E4) put on the other sheet?

 

[staticbob]

Like this mate, thanks for the help . .

Derby Uni PR.xls
	A	B	C	D	E
1		21/11/2003	28/11/2003
2	ErectHoardingtoSiteBoundary	5%	25%
3	Locate&MarkExistingServices	10%	25%
4	ErectionofExternalScaffold	50%	75%
5	SetupSiteCompound	100%	100%
6	AsbestosRemovaltoRearBays	0%	10%
7	AsbestosRemovaltoFrontBays	0%	10%
8	ExcavateforNewDrainage	0%	10%
9	LayPipes&ConnecttoExisting	0%	0%
10	Backfill&MakeGoodGround	0%	0%
11	StripOutRedundantServices&Sanitaryware	0%	0%
12	ShotBlastExtingSteelworktoRearBays	0%	0%
13	ShotBlastExtingSteelworktoFrontBays	0%	0%
14	DemolitionofRearNib	0%	0%
15	RemovalofJoinery&Stairs	0%	0%
16	DemolitionofExistingBlockwork	0%	0%
17	RemovalofConcreteBase	0%	0%
18	ExcavatetoReduceLoadingBayArea	0%	0%
19	RemoveEntranceDoorway	0%	0%
20	MakeGoodExternalBlockworkWalls	0%	0%
As Built data

 

[jimboy]

So you want to copy the data in column E to the first empty column in the As Built Data sheet (obviously matching the names in column A)?

Will the name in column A (Progress Report) always be in the As Built Data sheet?

 

[staticbob]

You got it mate. Each week, users will select the report date and update the actual %s. I then need to copy this onto the as built data page. The next available column will be fine, I can always pop-up a msgbox if the date is prior to the last report date.

On the original copy soln, I dont want to copy the whole rows, just the task list in column A. If this task list changes, the file will be re-created, so no real need to match task names as we paste in.

I have this code to do the initial Task list copy, but it isn't selecting the full list, just the first 14 or so.

Sheets("Progress Report").Range("A6:A" & Range("A6").End(xlDown).Row).Copy
ActiveSheet.Paste Destination:=Worksheets("As Built Data").Range("A2")

thanks again mate.

 

[jimboy]

You got it mate.

I thought I did, but that has confused me!

Why do you want to copy column A only?

I thought you wanted to copy the value in (Progress Report) column E to the As Built data sheet (first empty column) and insert the data (PR - C4) to row 1 (matching up the values in columns A)...

 

[staticbob]

Sorry mate, let me try to clarify. I need 2 things.

1 - Once the task list (A6:Axxx) on the progress report has been completed, I want to copy the task list, and paste it onto the already existing (but empty) As built Data page, into A2:Axxx. This is only done once when setting up.

2 - The report is updated weekly. When this is done, I want to copy the report date, and the actual %ages onto the As Built page, into the next available column. The As Built Page will essentially track the progress of the project from start to end, recording actua %ages on each report date. This is done on a "Confirm" button.

The rest of the data on the Progress Report sheet is not needed on the As built page. The As Built Page will simply be a list of the tasks, and then how complete each was, on each report date.

Hope thats a tad clearer matey, thanks for the help.

 

[jimboy]

This will do the second part i.e. copy col. E to the other sheet.

Sub test()
Dim c As Range, d As Range
Dim mycolumn As Integer
mycolumn = Sheets("As Built Data").Range("IV1").End(xlToLeft).Column + 1
Sheets("As Built data").Cells(1, mycolumn) = Sheets("Progress Report").Range("C4").Value
For Each c In Sheets("Progress Report").Range("A6:A" & Range("A65536").End(xlUp).Row)
For Each d In Sheets("As Built Data").Range("A2:A" & Range("A65536").End(xlUp).Row)
If c.Value = d.Value Then
Sheets("As Built Data").Cells(d.Row, mycolumn) = c.Offset(0, 4)
GoTo mynext
End If
Next d
mynext:
Next c
Sheets("As Built Data").Range(Cells(2, mycolumn), Cells(5000, mycolumn)).NumberFormat = "0.00%"
End Sub

If part one only need to be done once why can't you do this manually?

 

[staticbob]

It could be done manually if I was setting it up, but I want to eliminate as much user intervention as possible. The guys that are doing this aren't exactly the best when it comes to Excel . . I'm just trying to automate to make it more friendly.

Cheers
Bob

 

[jimboy]

Try this for part 1;

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

End Sub