Copy/Paste in VBA Excel

[March]

I have VBA code below

With Workbooks("Book.xls").Worksheets("Sheet1")
.Range("C1:BO1").Copy
.Range(strPrevRange).PasteSpecial (xlPasteFormulasAndNumberFormats)
End With

Application.CutCopyMode = False

With Workbooks("Book.xls").Worksheets("Sheet1")
.Range("C4:BO4").Copy
.Range(strCurrentRange).PasteSpecial (xlPasteFormulasAndNumberFormats)
End With

Application.CutCopyMode = False



With Workbooks("Book.xls").Worksheets("Sheet1")
.Range(strPrevRange).Copy
.Range(strPrevRange).PasteSpecial (xlPasteValues)
End With

---------
With strCurrentRange and strPrevRange are the range of new address.
Range(C1:BO1) and (C4:BO4) contains formula

from the code above I code in ThisWorkbook in Private Sub Workbook_Open()
once its open should be run.

The problem to run the process is I cannot get the values in range(C1:BO1)
update before range(C1:BO1).copy then range(xx:xx).PasteSpecial
(xlPasteFormulasAndNumberFormats)

and range(C1:BO1).copy then range(xx:xx).PasteSpecial (xlPasteValues) at the
end

I try to have "Application.Wait Now + TimeValue("00:00:10")" in the begining
of the code .... I got the same result that the value not update before copy.


However, when I run the code line-by-line, everything seems to be updated ....

This I have no idea how to deal with it. Please give me suggestion.


Thanks


March

 

[Gary''s Student]

Clearly if you have code in the Workbook Open event, Excel must decide if the
sheet should be re-calculated first or the copy/paste performed first. Let's
try the following:

Private Sub Workbook_Open()
Application.CalculateFullRebuild
DoEvents
'
' your copy/pastes and anything else
'
End Sub

This may not work, but its worth a try!

 

[March]

Still not work.

Clearly if you have code in the Workbook Open event, Excel must decide if the
sheet should be re-calculated first or the copy/paste performed first. Let's
try the following:

Private Sub Workbook_Open()
Application.CalculateFullRebuild
DoEvents
'
' your copy/pastes and anything else
'
End Sub

This may not work, but its worth a try!

 

[Rick Rothstein]

If I understand what your are trying to do (it is **always** a good idea to
explain what non-working code is trying to do so we don't have to guess),
you can probably use the Calculate method to force the update. Also, for
what I think you are trying to do, you don't really need to use
Copy/PasteSpecial. Give this a try and see how it works...

With Workbooks("Book.xls").Worksheets("Sheet1")
.Range("C1:BO1").Copy .Range(strPrevRange)
.Range("C4:BO4").Copy .Range(strCurrentRange)
.Calculate
.Range(strPrevRange).Value = .Range(strPrevRange).Value
End With

I'm not sure about the need for the .Calculate method or its location.
Perhaps it needs to be the first line of the code since you are executing
the code in the Open event. Or, perhaps, it needs to be where I have *and*
again as the first line of code. Perhaps, even, it will not be needed at all
using the above code. Note having your data (and being too lazy to set up an
example), the above code is more off the top of my head than actually
tested. Try it... play around with the .Calculate method (don't forget the
leading 'dot' so only the worksheet itself is recalculated)... and see if it
works for you or not.

 

[Gary''s Student]

Do you have more than one workbook open?
Could it be re-calculating the wrong book??

 

[March]

no ... i have only one workbook open.

However, after the process run... then all values are updated. ????

 

[March]

the formula is add-in from other program ... let talk about eSignal something
like that.... do you have any idea about "Winros"?

 

[Rick Rothstein]

Sorry, I never heard of it.

 

[jaf]

Hi,
Give this a try.

Private Sub Workbook_Open()
DoEvents
'rest of your code


John

 

[March]

It didn't work....I think it might because of the workbook linked to another
source link eSignal.