Code help

staticbob

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

I have this code (from JimBoy) that I use to copy data from one sheet to another. The source data is updated weekly, I am using the code to copy the updated cells to a new sheet so I have a log of progress.

The code works, it copies some data, but not all. it also debugs on the line after next c with the error - Application or object defined error.

All I am trying to do is copy cells E6:E102 (last entry) from the sheet "Progress Report" onto the next available column in the sheet "As Built data". This will obviously build-up each week.

Any help would be much appreciated !

Bob

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%"
ActiveSheet.Protect Password:="apples"
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
With Sheets("As Built data")
.Range(.Cells(2, mycolumn), .Cells(5000, mycolumn)).NumberFormat = "0.00%"
End With
 
Upvote 0
Thanks PN,

Thats sorted the debug error, but it still doesn't paste all the data. For example, I have data in D6:D104 it copies and pastes most data correctly, but leaves some cells blank when pasting ???

Cells A2:A52 paste OK
Cells A53:A56 leaves blank
A56:A59 OK
A60 blank
A61:A90 OK
91-93 blank.

I can't think that this can be a problem with the code, but I have checked the format and data in the source cells and all looks OK ???

Cheers
Bob.
 
Upvote 0
The code is copying cells from column E of "Progress Report" to the next available column of "As Built data" provided that the data in the column A cells on each sheet are the same.

Is this what you want done?
If yes, but it is not producing what you expect, suggest that you step through the macro via F8 to see exactly what it is doing.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top