Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/Paste in VBA Excel
Hi,
Give this a try. Private Sub Workbook_Open() DoEvents 'rest of your code John |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
copy & paste in excel | Excel Discussion (Misc queries) | |||
Copy and Paste in Excel | Excel Discussion (Misc queries) | |||
Excel cut/Paste Problem: Year changes after data is copy and paste | Excel Discussion (Misc queries) | |||
copy multiple worksheets of a workbook, and paste onto a Word document ( either create new doc file or paste onto an existing file.) I need this done by VBA, Excel Macro | Excel Programming |