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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,817
Messages
5,772,456
Members
425,760
Latest member
paphon

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