Copy/Close error

[LiAD]

Hi,

I have the following code which is supposed to active on file close to
copy/paste any values in cols in cols AG-AU if the value in col AU of that
row = 1.

I have my sheets named Sheet1 etc to match this code - even if its not the
sheet names I actually want to use.

Could some-one tell me why this may not be working?

It shows an error (highlight in yellow) in these two rows
..Range(MyCell.Offset(0, -14).Address & ":" & MyCell.Address).Value =
..Range(MyCell.Offset(0, -14).Address & ":" & MyCell.Address).Value

Thanks
LiAD

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim i As Long, MyCell As Range
For i = 2 To 4 Step 1
With Sheets("Sheet" & i)
For Each MyCell In .Range("AU10:A" & .Range("AU" & Rows.Count).End(xlUp).Row)
If MyCell = 1 Then
..Range(MyCell.Offset(0, -14).Address & ":" & MyCell.Address).Value =
..Range(MyCell.Offset(0, -14).Address & ":" & MyCell.Address).Value
End If
Next MyCell
End With
Next i
ThisWorkbook.Save
End Sub

 

[JLGWhiz]

I think you might have an typo in this line. The range specified starts in
Column A as it is written below. The For ... Each loop will begin in Cell
A10 and cannot offset to the left. That is what is causing the error
message.


For Each MyCell In .Range("AU10:A" & .Range("AU" &
Rows.Count).End(xlUp).Row)

Maybe you only wanted Column AU in which case the line above would be
modified to:


For Each MyCell In .Range("AU10:AU" & .Range("AU" &
Rows.Count).End(xlUp).Row)

 

[Otto Moehrbach]

What does the error message say? A range of cells cannot have a value.
Only a single cell can have a value. You will get a "Type Mismatch" error
with this code. What do you want to accomplish with these 2 lines of code?
HTH Otto

 

[joel]

You are copying the from the dame address to the same address is tha
what you really wnat?


code should look like this


..Range(MyCell.Offset(0, -14)).Value = ..Range(MyCell.Offset(0
-14)).Value


You could be getting errors for these reasons

1) MyCell column - 14 is 0 or less
2) MyCel is not set defined
3) The workbook that Mycell was set to is closed (2nd parent)
4) The sheet that Mycell is set to is set to is not a workshee
(1st parent

 

[LiAD]

Hi,

Thanks for your help.

If I try the the two dots ..Range as suggested it says syntax error. If I
delete the second dot and then try to run the code, (i.e. the code below) it
says Application or object defined error. In col AG I have just text values
(months of the year), could this affect it?

LiAD

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim i As Long, MyCell As Range
For i = 1 To 3 Step 1
With Sheets("Sheet" & i)
For Each MyCell In .Range("AU10:AU" & .Range("AU" & Rows.Count).End(xlUp).Row)
If MyCell = 1 Then
..Range(MyCell.Offset(0, -14)).Value = .Range(MyCell.Offset(0, -14)).Value
End If
Next MyCell
End With
Next i
ThisWorkbook.Save
End Sub

 

[LiAD]

Yes I want to copy paste any formulas that return a greater than zero value
if the value in AU is = 1.

The ..Range you have suggested I cannot get to work.

The col -14 (which i guess is -14 from AU so its AG has text in it (months
of the year) so your first point maybe be a issue but this would need a code
change.
The other items for MyCell i dont get how this works.

Does this help you to help me at all?

 

[LiAD]

OK. Now the code I have is below and it seems to be running - its just not
doing exactly what I'd hoped.

If I watch two of the columns that have data in them, (0.0 or a value), when
the code runs it replaces the data with #REF. Any cells that do not have a
number in these two columns also show #REF, but this is normal as no data is
yet available for these days (the date is greater than yesterdays date).
Somehow there is an issue with the copy/paste values. For info the data is
copied from the source file using INDIRECT(). The same happens on all three
sheets.

Two other small issues
- it is very slow to complete this operation. Is there any way of speeding
it up?
- if I want to change the sheet names from Sheet1 to a,b,c what is the best
way of doing this? (I possible i'd rather that the code referred to sheet
2-4 etc but the sheet name can be anything and be changeable without having
to alter the code - its sheet 2-4 that are of interest for now).

In the future I may have about 15 sheets, hence the need for speed and
changeable sheet names.

Would you have any ideas on these three points?
Thanks for your help
LiAD

 

[LiAD]

Sorry forgot the code

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim i As Long, MyCell As Range
For i = 1 To 3 Step 1
With Sheets("Sheet" & i)
For Each MyCell In .Range("AU10:AU" & .Range("AU" & Rows.Count).End(xlUp).Row)
If MyCell = 1 Then
..Range(MyCell.Offset(0, -14).Address & ":" & MyCell.Address).Value =
..Range(MyCell.Offset(0, -14).Address & ":" & MyCell.Address).Value
End If
Next MyCell
End With
Next i
ThisWorkbook.Save
End Sub

 

[joel]

It can't be the copy function because there is no copy

..Range(MyCell.Offset(0, -14).Address & ":" & MyCell.Address).Value =
..Range(MyCell.Offset(0, -14).Address & ":" & MyCell.Address).Value


Your source and destination cells is exactly the same. the only thin
this instruction does is to remove the formula from the cell. The RE
must be there before the instruction is executed.

The instruction should be rewiritten as follows

Range(MyCell.Offset(0, -14),MyCell).copy
MyCell.Offset(0, -14).PasteSpecial Paste:=xlpastevalue

 

[LiAD]

Hi,

Thanks a lot, now copy paste is working well.

Could you tell me is there any way of speeding it up and having changeable
sheet names?

Cheers
LiAD