VBA Copy data

staticbob

Well-known Member
Joined
Oct 7, 2003
Messages
1,079
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
ABCDEF
1SiteNameMarkeatonCampus-UniversiyofDerby
2ProjectNumber RelatedProgrammeRevisionE32012 ConstructionProgrammeRev1st
3ReportDateAverageActual
412/12/20035%11%
5ActivityStartDateFinishDateScheduleActualSlippage (Days)
6ErectHoardingtoSiteBoundary24/11/200308:0026/11/200317:00100%100%0
7Locate&MarkExistingServices24/11/200308:0026/11/200317:00100%100%0
8ErectionofExternalScaffold08/12/200308:0019/12/200317:0050%100%-5
9SetupSiteCompound05/01/200408:0007/01/200417:000%100%-3
10AsbestosRemovaltoRearBays20/11/200308:0005/12/200317:00100%100%0
11AsbestosRemovaltoFrontBays08/12/200308:0028/01/200417:0013%100%-33
12ExcavateforNewDrainage08/12/200308:0009/01/200417:0020%100%-20
13LayPipes&ConnecttoExisting07/01/200408:0014/01/200417:000%0%0
14Backfill&MakeGoodGround05/01/200408:0023/01/200417:000%0%0
15StripOutRedundantServices&Sanitaryware08/12/200308:0019/12/200317:0050%100%-5
Progress Report
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

jimboy

Well-known Member
Joined
Apr 11, 2002
Messages
2,314
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;

Code:
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

Well-known Member
Joined
Oct 7, 2003
Messages
1,079
Like this mate, thanks for the help . .
Derby Uni PR.xls
ABCDE
121/11/200328/11/2003
2ErectHoardingtoSiteBoundary5%25%
3Locate&MarkExistingServices10%25%
4ErectionofExternalScaffold50%75%
5SetupSiteCompound100%100%
6AsbestosRemovaltoRearBays0%10%
7AsbestosRemovaltoFrontBays0%10%
8ExcavateforNewDrainage0%10%
9LayPipes&ConnecttoExisting0%0%
10Backfill&MakeGoodGround0%0%
11StripOutRedundantServices&Sanitaryware0%0%
12ShotBlastExtingSteelworktoRearBays0%0%
13ShotBlastExtingSteelworktoFrontBays0%0%
14DemolitionofRearNib0%0%
15RemovalofJoinery&Stairs0%0%
16DemolitionofExistingBlockwork0%0%
17RemovalofConcreteBase0%0%
18ExcavatetoReduceLoadingBayArea0%0%
19RemoveEntranceDoorway0%0%
20MakeGoodExternalBlockworkWalls0%0%
As Built data
 

jimboy

Well-known Member
Joined
Apr 11, 2002
Messages
2,314
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

Well-known Member
Joined
Oct 7, 2003
Messages
1,079

ADVERTISEMENT

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

Well-known Member
Joined
Apr 11, 2002
Messages
2,314
staticbob said:
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

Well-known Member
Joined
Oct 7, 2003
Messages
1,079

ADVERTISEMENT

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

Well-known Member
Joined
Apr 11, 2002
Messages
2,314
This will do the second part i.e. copy col. E to the other sheet.

Code:
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

Well-known Member
Joined
Oct 7, 2003
Messages
1,079
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

Well-known Member
Joined
Apr 11, 2002
Messages
2,314
Try this for part 1;

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,112,798
Messages
5,542,569
Members
410,560
Latest member
1ndependent
Top