Copy/Paste in VBA Excel
 

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

Copy/Paste in VBA Excel
 

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!
--
Gary''s Student - gsnu200827


"March" wrote:

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

Copy/Paste in VBA Excel
 

Still not work.




"Gary''s Student" wrote:

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!
--
Gary''s Student - gsnu200827


"March" wrote:

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

Copy/Paste in VBA Excel
 

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.

--
Rick (MVP - Excel)


"March" wrote in message
...
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

Copy/Paste in VBA Excel
 

Do you have more than one workbook open?
Could it be re-calculating the wrong book??
--
Gary''s Student - gsnu200827


"March" wrote:

Still not work.




"Gary''s Student" wrote:

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!
--
Gary''s Student - gsnu200827


"March" wrote:

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

Copy/Paste in VBA Excel
 

Surely with this:
Code:
--------------------
With Workbooks("Book.xls").Worksheets("Sheet1")
.Range(strPrevRange).Copy
.Range(strPrevRange).PasteSpecial (xlPasteValues)
End With

--------------------
you meant:

Code:
--------------------
With Workbooks("Book.xls").Worksheets("Sheet1")
.Range(strPrevRange) = .Range(strPrevRange).Value
End With

--------------------
to remove all the formulae?March;184099 Wrote:
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


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=50861

Copy/Paste in VBA Excel
 

no ... i have only one workbook open.

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


"Gary''s Student" wrote:

Do you have more than one workbook open?
Could it be re-calculating the wrong book??
--
Gary''s Student - gsnu200827


"March" wrote:

Still not work.




"Gary''s Student" wrote:

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!
--
Gary''s Student - gsnu200827


"March" wrote:

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

Copy/Paste in VBA Excel
 

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

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"March" wrote in message
...
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

Copy/Paste in VBA Excel
 

Sorry, I never heard of it.

--
Rick (MVP - Excel)


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

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"March" wrote in message
...
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

Copy/Paste in VBA Excel
 

Hi,
Give this a try.

Private Sub Workbook_Open()
DoEvents
'rest of your code


John

Copy/Paste in VBA Excel
 

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





"jaf" wrote:

Hi,
Give this a try.

Private Sub Workbook_Open()
DoEvents
'rest of your code


John

Copy/Paste in VBA Excel
 

Did you see this post?Simon Lloyd;184186 Wrote:
Surely with this:
Code:
--------------------
With Workbooks("Book.xls").Worksheets("Sheet1")
.Range(strPrevRange).Copy
.Range(strPrevRange).PasteSpecial (xlPasteValues)
End With

--------------------
you meant:

Code:
--------------------
With Workbooks("Book.xls").Worksheets("Sheet1")
.Range(strPrevRange) = .Range(strPrevRange).Value
End With

--------------------
to remove all the formulae?


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=50861