| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to stop the screen from 'flashing' when copying and pasting
A bit of background:
I have created a worksheet that allows people to record the relevant details when carrying out audits. The information that they complete is located in various cells in the worksheet. These audits then need to be collated and analyised on a 'master' document. I have written a Macro that activates the audit form, selects the relevant cell, copies the information and then activates the master document, pastes the values of the cell in the next available column, then repeats this process until all the relevant cells have been copied. An example of the code is below : Workbooks("Audit.xls").Activate Range("C3").Select Selection.Copy Workbooks("Master.xls").Activate Worksheets("VHOOC").Activate NextColumn = Application.WorksheetFunction.CountA(Range("1:1")) + 1 Cells(1, NextColumn).Select Selection.PasteSpecial Paste:=xlPasteValues Workbooks("Audit.xls").Activate Range("H5:H8").Select Selection.Copy Workbooks("Master.xls").Activate Cells(2, NextColumn).Select Selection.PasteSpecial Paste:=xlPasteValues Workbooks("Audit.xls").Activate etc, etc, etc I did it like this as the cells that need copying are in multiple ranges and I don't know how to select many ranges in one go. The above code works and it does what I want it to. The problem I have is that because it Activates the worksheets the screen constantly 'flashes' between the two,as it automates the select copy paste proceedure. Is there a way to stop this? Can I get excel to select, copy and paste the cells without the screen changing? Sorry this goes on a bit but I hope it gives an accurate description of what I want to stop happening. I have only been learning the Macro side of things for two weeks out of a book, so please bear with me! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to stop the screen from 'flashing' when copying and pasting
Try this
Applicaiton.Screenupdating = false '**** Workbooks("Audit.xls").Activate Range("C3").Select Selection.Copy Workbooks("Master.xls").Activate Worksheets("VHOOC").Activate NextColumn = Application.WorksheetFunction.CountA(Range("1:1")) + 1 Cells(1, NextColumn).Select Selection.PasteSpecial Paste:=xlPasteValues Workbooks("Audit.xls").Activate Range("H5:H8").Select Selection.Copy Workbooks("Master.xls").Activate Cells(2, NextColumn).Select Selection.PasteSpecial Paste:=xlPasteValues Workbooks("Audit.xls").Activate Applicaiton.Screenupdating = true '**** -- HTH... Jim Thomlinson "BaggieDan" wrote: A bit of background: I have created a worksheet that allows people to record the relevant details when carrying out audits. The information that they complete is located in various cells in the worksheet. These audits then need to be collated and analyised on a 'master' document. I have written a Macro that activates the audit form, selects the relevant cell, copies the information and then activates the master document, pastes the values of the cell in the next available column, then repeats this process until all the relevant cells have been copied. An example of the code is below : Workbooks("Audit.xls").Activate Range("C3").Select Selection.Copy Workbooks("Master.xls").Activate Worksheets("VHOOC").Activate NextColumn = Application.WorksheetFunction.CountA(Range("1:1")) + 1 Cells(1, NextColumn).Select Selection.PasteSpecial Paste:=xlPasteValues Workbooks("Audit.xls").Activate Range("H5:H8").Select Selection.Copy Workbooks("Master.xls").Activate Cells(2, NextColumn).Select Selection.PasteSpecial Paste:=xlPasteValues Workbooks("Audit.xls").Activate etc, etc, etc I did it like this as the cells that need copying are in multiple ranges and I don't know how to select many ranges in one go. The above code works and it does what I want it to. The problem I have is that because it Activates the worksheets the screen constantly 'flashes' between the two,as it automates the select copy paste proceedure. Is there a way to stop this? Can I get excel to select, copy and paste the cells without the screen changing? Sorry this goes on a bit but I hope it gives an accurate description of what I want to stop happening. I have only been learning the Macro side of things for two weeks out of a book, so please bear with me! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to stop the screen from 'flashing' when copying and pasting
Put this in the start of your code
application.screenupdating = false Put this in the end of your code application.screenupdating = true "BaggieDan" wrote: A bit of background: I have created a worksheet that allows people to record the relevant details when carrying out audits. The information that they complete is located in various cells in the worksheet. These audits then need to be collated and analyised on a 'master' document. I have written a Macro that activates the audit form, selects the relevant cell, copies the information and then activates the master document, pastes the values of the cell in the next available column, then repeats this process until all the relevant cells have been copied. An example of the code is below : Workbooks("Audit.xls").Activate Range("C3").Select Selection.Copy Workbooks("Master.xls").Activate Worksheets("VHOOC").Activate NextColumn = Application.WorksheetFunction.CountA(Range("1:1")) + 1 Cells(1, NextColumn).Select Selection.PasteSpecial Paste:=xlPasteValues Workbooks("Audit.xls").Activate Range("H5:H8").Select Selection.Copy Workbooks("Master.xls").Activate Cells(2, NextColumn).Select Selection.PasteSpecial Paste:=xlPasteValues Workbooks("Audit.xls").Activate etc, etc, etc I did it like this as the cells that need copying are in multiple ranges and I don't know how to select many ranges in one go. The above code works and it does what I want it to. The problem I have is that because it Activates the worksheets the screen constantly 'flashes' between the two,as it automates the select copy paste proceedure. Is there a way to stop this? Can I get excel to select, copy and paste the cells without the screen changing? Sorry this goes on a bit but I hope it gives an accurate description of what I want to stop happening. I have only been learning the Macro side of things for two weeks out of a book, so please bear with me! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to stop the screen from 'flashing' when copying and pasting
Hi
Application.Screenupdating = False Code Application.ScreenUpdating = True You can simplify your code and prevent all the activating and selecting etc. by setting up what your Source and destination worksheets are, then explicitly say Source = Destination.value as below Dim wbS As Workbook, wbD As Workbook Dim wSs As Worksheet, wDs As Worksheet Dim Nextcolumn As Long Application.ScreenUpdating = False Set wbS = Workbooks("Audit.xls") ' Source book Set wbD = Workbooks("Master.xls") ' destination book Set wSs = wbS.Sheets("Sheet1") ' Change to Source Sheet name Set wDs = wbD.Sheets("VHOOC") ' Destination Sheet Nextcolumn = wDs.Cells(1, 1).End(xlToRight).Column + 1 wDs.Cells(1, Nextcolumn) = wSs.Range("C3").Value wDs.Cells(2, Nextcolumn) = wSs.Range("H5:H8").Value 'etc. 'etc. Application.ScreenUpdating = True -- Regards Roger Govier "BaggieDan" wrote in message ... A bit of background: I have created a worksheet that allows people to record the relevant details when carrying out audits. The information that they complete is located in various cells in the worksheet. These audits then need to be collated and analyised on a 'master' document. I have written a Macro that activates the audit form, selects the relevant cell, copies the information and then activates the master document, pastes the values of the cell in the next available column, then repeats this process until all the relevant cells have been copied. An example of the code is below : Workbooks("Audit.xls").Activate Range("C3").Select Selection.Copy Workbooks("Master.xls").Activate Worksheets("VHOOC").Activate NextColumn = Application.WorksheetFunction.CountA(Range("1:1")) + 1 Cells(1, NextColumn).Select Selection.PasteSpecial Paste:=xlPasteValues Workbooks("Audit.xls").Activate Range("H5:H8").Select Selection.Copy Workbooks("Master.xls").Activate Cells(2, NextColumn).Select Selection.PasteSpecial Paste:=xlPasteValues Workbooks("Audit.xls").Activate etc, etc, etc I did it like this as the cells that need copying are in multiple ranges and I don't know how to select many ranges in one go. The above code works and it does what I want it to. The problem I have is that because it Activates the worksheets the screen constantly 'flashes' between the two,as it automates the select copy paste proceedure. Is there a way to stop this? Can I get excel to select, copy and paste the cells without the screen changing? Sorry this goes on a bit but I hope it gives an accurate description of what I want to stop happening. I have only been learning the Macro side of things for two weeks out of a book, so please bear with me! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to stop the screen from 'flashing' when copying and pastin
Thank you all, as its bed time now I'll give them a go tomorrow.
Thannks again!! "Roger Govier" wrote: Hi Application.Screenupdating = False Code Application.ScreenUpdating = True You can simplify your code and prevent all the activating and selecting etc. by setting up what your Source and destination worksheets are, then explicitly say Source = Destination.value as below Dim wbS As Workbook, wbD As Workbook Dim wSs As Worksheet, wDs As Worksheet Dim Nextcolumn As Long Application.ScreenUpdating = False Set wbS = Workbooks("Audit.xls") ' Source book Set wbD = Workbooks("Master.xls") ' destination book Set wSs = wbS.Sheets("Sheet1") ' Change to Source Sheet name Set wDs = wbD.Sheets("VHOOC") ' Destination Sheet Nextcolumn = wDs.Cells(1, 1).End(xlToRight).Column + 1 wDs.Cells(1, Nextcolumn) = wSs.Range("C3").Value wDs.Cells(2, Nextcolumn) = wSs.Range("H5:H8").Value 'etc. 'etc. Application.ScreenUpdating = True -- Regards Roger Govier "BaggieDan" wrote in message ... A bit of background: I have created a worksheet that allows people to record the relevant details when carrying out audits. The information that they complete is located in various cells in the worksheet. These audits then need to be collated and analyised on a 'master' document. I have written a Macro that activates the audit form, selects the relevant cell, copies the information and then activates the master document, pastes the values of the cell in the next available column, then repeats this process until all the relevant cells have been copied. An example of the code is below : Workbooks("Audit.xls").Activate Range("C3").Select Selection.Copy Workbooks("Master.xls").Activate Worksheets("VHOOC").Activate NextColumn = Application.WorksheetFunction.CountA(Range("1:1")) + 1 Cells(1, NextColumn).Select Selection.PasteSpecial Paste:=xlPasteValues Workbooks("Audit.xls").Activate Range("H5:H8").Select Selection.Copy Workbooks("Master.xls").Activate Cells(2, NextColumn).Select Selection.PasteSpecial Paste:=xlPasteValues Workbooks("Audit.xls").Activate etc, etc, etc I did it like this as the cells that need copying are in multiple ranges and I don't know how to select many ranges in one go. The above code works and it does what I want it to. The problem I have is that because it Activates the worksheets the screen constantly 'flashes' between the two,as it automates the select copy paste proceedure. Is there a way to stop this? Can I get excel to select, copy and paste the cells without the screen changing? Sorry this goes on a bit but I hope it gives an accurate description of what I want to stop happening. I have only been learning the Macro side of things for two weeks out of a book, so please bear with me! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Screen "Flashing" on Window change | Excel Discussion (Misc queries) | |||
| Screen flashing why? | Excel Discussion (Misc queries) | |||
| remove flashing border after copying cells, how? | Excel Discussion (Misc queries) | |||
| Opening Excel changes screen resolution. How do I stop this ? | Excel Discussion (Misc queries) | |||
| stop macro from flashing sheets | Excel Discussion (Misc queries) | |||
Hybrid Mode
