Macro change problem
 

This Macro uses a Column "E" of unique numbers in Archive sheet for any
changes in Current sheet and if it finds any overwites the data in the
corresponding rows on Archive sheet.

Both sheets currently have 31 columns.
I need to remove the B column from both sheets which means the unique
numbers in Column E will now be Column D and both sheets down to 30
columns.

I changed references to "E" in the script to "D" and the Resize values to
30,
but it does'nt work, I get an error message and when I debug the highlited
line is :

Dn1.Offset(, -4).Resize(, 30).Copy Dn2.Offset(, -4).Resize(, 30)

anybody see where i'm going wrong and set me on the
right track

Many thanks for your help



this is the original without the changes I made

Sub synchronize()
Dim Rng1 As Range, Dn1 As Range, Rng2 As Range, Dn2 As Range
With Sheets("Current") '1
Set Rng1 = .Range(.Range("E1"), .Range("E" & Rows.Count).End(xlUp))
End With

With Sheets("Archive") '2
Set Rng2 = .Range(.Range("E1"), .Range("E" & Rows.Count).End(xlUp))
End With
For Each Dn2 In Rng2
For Each Dn1 In Rng1
If UCase(Trim(Dn2)) = UCase(Trim(Dn1)) Then
Dn1.Offset(, -4).Resize(, 31).Copy Dn2.Offset(, -4).Resize(,
31)

End If
Next Dn1
Next Dn2
End Sub

Macro change problem
 

Change the , -4).Resize to ,-3).Resize in both instances of that statement.
When you changed E to D that caused "Current column number minus 4" to equate
to zero, and there is no column zero.

"Dr Hackenbush" wrote:

This Macro uses a Column "E" of unique numbers in Archive sheet for any
changes in Current sheet and if it finds any overwites the data in the
corresponding rows on Archive sheet.

Both sheets currently have 31 columns.
I need to remove the B column from both sheets which means the unique
numbers in Column E will now be Column D and both sheets down to 30
columns.

I changed references to "E" in the script to "D" and the Resize values to
30,
but it does'nt work, I get an error message and when I debug the highlited
line is :

Dn1.Offset(, -4).Resize(, 30).Copy Dn2.Offset(, -4).Resize(, 30)

anybody see where i'm going wrong and set me on the
right track

Many thanks for your help



this is the original without the changes I made

Sub synchronize()
Dim Rng1 As Range, Dn1 As Range, Rng2 As Range, Dn2 As Range
With Sheets("Current") '1
Set Rng1 = .Range(.Range("E1"), .Range("E" & Rows.Count).End(xlUp))
End With

With Sheets("Archive") '2
Set Rng2 = .Range(.Range("E1"), .Range("E" & Rows.Count).End(xlUp))
End With
For Each Dn2 In Rng2
For Each Dn1 In Rng1
If UCase(Trim(Dn2)) = UCase(Trim(Dn1)) Then
Dn1.Offset(, -4).Resize(, 31).Copy Dn2.Offset(, -4).Resize(,
31)

End If
Next Dn1
Next Dn2
End Sub



.

Macro change problem
 

That did it, i should have picked up on that.

Thanks very much for your help


"JLatham" wrote in message
...
Change the , -4).Resize to ,-3).Resize in both instances of that
statement.
When you changed E to D that caused "Current column number minus 4" to
equate
to zero, and there is no column zero.

"Dr Hackenbush" wrote:

This Macro uses a Column "E" of unique numbers in Archive sheet for any
changes in Current sheet and if it finds any overwites the data in the
corresponding rows on Archive sheet.

Both sheets currently have 31 columns.
I need to remove the B column from both sheets which means the unique
numbers in Column E will now be Column D and both sheets down to 30
columns.

I changed references to "E" in the script to "D" and the Resize values to
30,
but it does'nt work, I get an error message and when I debug the
highlited
line is :

Dn1.Offset(, -4).Resize(, 30).Copy Dn2.Offset(, -4).Resize(, 30)

anybody see where i'm going wrong and set me on the
right track

Many thanks for your help



this is the original without the changes I made

Sub synchronize()
Dim Rng1 As Range, Dn1 As Range, Rng2 As Range, Dn2 As Range
With Sheets("Current") '1
Set Rng1 = .Range(.Range("E1"), .Range("E" & Rows.Count).End(xlUp))
End With

With Sheets("Archive") '2
Set Rng2 = .Range(.Range("E1"), .Range("E" & Rows.Count).End(xlUp))
End With
For Each Dn2 In Rng2
For Each Dn1 In Rng1
If UCase(Trim(Dn2)) = UCase(Trim(Dn1)) Then
Dn1.Offset(, -4).Resize(, 31).Copy
Dn2.Offset(, -4).Resize(,
31)

End If
Next Dn1
Next Dn2
End Sub



.

Macro change problem
 

Just glad I could help.

"Dr Hackenbush" wrote:

That did it, i should have picked up on that.

Thanks very much for your help


"JLatham" wrote in message
...
Change the , -4).Resize to ,-3).Resize in both instances of that
statement.
When you changed E to D that caused "Current column number minus 4" to
equate
to zero, and there is no column zero.

"Dr Hackenbush" wrote:

This Macro uses a Column "E" of unique numbers in Archive sheet for any
changes in Current sheet and if it finds any overwites the data in the
corresponding rows on Archive sheet.

Both sheets currently have 31 columns.
I need to remove the B column from both sheets which means the unique
numbers in Column E will now be Column D and both sheets down to 30
columns.

I changed references to "E" in the script to "D" and the Resize values to
30,
but it does'nt work, I get an error message and when I debug the
highlited
line is :

Dn1.Offset(, -4).Resize(, 30).Copy Dn2.Offset(, -4).Resize(, 30)

anybody see where i'm going wrong and set me on the
right track

Many thanks for your help



this is the original without the changes I made

Sub synchronize()
Dim Rng1 As Range, Dn1 As Range, Rng2 As Range, Dn2 As Range
With Sheets("Current") '1
Set Rng1 = .Range(.Range("E1"), .Range("E" & Rows.Count).End(xlUp))
End With

With Sheets("Archive") '2
Set Rng2 = .Range(.Range("E1"), .Range("E" & Rows.Count).End(xlUp))
End With
For Each Dn2 In Rng2
For Each Dn1 In Rng1
If UCase(Trim(Dn2)) = UCase(Trim(Dn1)) Then
Dn1.Offset(, -4).Resize(, 31).Copy
Dn2.Offset(, -4).Resize(,
31)

End If
Next Dn1
Next Dn2
End Sub



.



.