software

Hello again,

I currently have a few macros which copy and paste data from one sheet to
another.

When the run you see the sheets flicking between each other as the macro
selects the active sheet, copies the selected range, then selects the
destination sheet pastes etc etc.

This all works as it should but is there a way of doing it in the background
whilst the user just sees the main input sheet and not flicking pages.

This would seriously help with saving my eye sight

Messy(Macro) as below

Sub transfer()
'
' transfer Macro
'
'

'
Range(quot;H11quot;).Select
Selection.Copy
Sheets(quot;Quotequot;).Select
Range(quot;B48:M48quot;).Select
ActiveSheet.Paste
Sheets(quot;Sheet1quot;).Select
Range(quot;H12quot;).Select
Selection.Copy
Sheets(quot;Quotequot;).Select
Range(quot;B50:M50quot;).Select
ActiveSheet.Paste
Sheets(quot;Sheet1quot;).Select
Range(quot;H13quot;).Select
Selection.Copy
Sheets(quot;Quotequot;).Select
Range(quot;B51:M51quot;).Select
ActiveSheet.Paste
Sheets(quot;Sheet1quot;).Select
Range(quot;H14quot;).Select
Selection.Copy
Sheets(quot;Quotequot;).Select
Range(quot;B52:M52quot;).Select
ActiveSheet.Paste
Sheets(quot;Sheet1quot;).Select
Range(quot;H15quot;).Select
Selection.Copy
Sheets(quot;Quotequot;).Select
Range(quot;B53:M53quot;).Select
ActiveSheet.Paste
Sheets(quot;Sheet1quot;).Select
Range(quot;H16quot;).Select
Selection.Copy
Sheets(quot;Quotequot;).Select
Range(quot;B54:M54quot;).Select
ActiveSheet.Paste
Sheets(quot;Sheet1quot;).Select
Range(quot;H17quot;).Select
Selection.Copy
Sheets(quot;Quotequot;).Select
Range(quot;B55:M55quot;).Select
ActiveSheet.Paste
Sheets(quot;Sheet1quot;).Select
Range(quot;H18quot;).Select
Selection.Copy
Sheets(quot;Quotequot;).Select
Range(quot;B56quot;).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets(quot;Quotequot;).Select
Range(quot;N47:U47quot;).Select

End Subhi,

could you copy the range H12:H18 and paste it into your other cells on the
Quote sheet in one job lot? are the cells on quote book B**:M** merged cells?

regs,

Nigelquot;Alarmblokequot; wrote:

gt; Hello again,
gt;
gt; I currently have a few macros which copy and paste data from one sheet to
gt; another.
gt;
gt; When the run you see the sheets flicking between each other as the macro
gt; selects the active sheet, copies the selected range, then selects the
gt; destination sheet pastes etc etc.
gt;
gt; This all works as it should but is there a way of doing it in the background
gt; whilst the user just sees the main input sheet and not flicking pages.
gt;
gt; This would seriously help with saving my eye sight
gt;
gt; Messy(Macro) as below
gt;
gt; Sub transfer()
gt; '
gt; ' transfer Macro
gt; '
gt; '
gt;
gt; '
gt; Range(quot;H11quot;).Select
gt; Selection.Copy
gt; Sheets(quot;Quotequot;).Select
gt; Range(quot;B48:M48quot;).Select
gt; ActiveSheet.Paste
gt; Sheets(quot;Sheet1quot;).Select
gt; Range(quot;H12quot;).Select
gt; Selection.Copy
gt; Sheets(quot;Quotequot;).Select
gt; Range(quot;B50:M50quot;).Select
gt; ActiveSheet.Paste
gt; Sheets(quot;Sheet1quot;).Select
gt; Range(quot;H13quot;).Select
gt; Selection.Copy
gt; Sheets(quot;Quotequot;).Select
gt; Range(quot;B51:M51quot;).Select
gt; ActiveSheet.Paste
gt; Sheets(quot;Sheet1quot;).Select
gt; Range(quot;H14quot;).Select
gt; Selection.Copy
gt; Sheets(quot;Quotequot;).Select
gt; Range(quot;B52:M52quot;).Select
gt; ActiveSheet.Paste
gt; Sheets(quot;Sheet1quot;).Select
gt; Range(quot;H15quot;).Select
gt; Selection.Copy
gt; Sheets(quot;Quotequot;).Select
gt; Range(quot;B53:M53quot;).Select
gt; ActiveSheet.Paste
gt; Sheets(quot;Sheet1quot;).Select
gt; Range(quot;H16quot;).Select
gt; Selection.Copy
gt; Sheets(quot;Quotequot;).Select
gt; Range(quot;B54:M54quot;).Select
gt; ActiveSheet.Paste
gt; Sheets(quot;Sheet1quot;).Select
gt; Range(quot;H17quot;).Select
gt; Selection.Copy
gt; Sheets(quot;Quotequot;).Select
gt; Range(quot;B55:M55quot;).Select
gt; ActiveSheet.Paste
gt; Sheets(quot;Sheet1quot;).Select
gt; Range(quot;H18quot;).Select
gt; Selection.Copy
gt; Sheets(quot;Quotequot;).Select
gt; Range(quot;B56quot;).Select
gt; Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
gt; SkipBlanks _
gt; :=False, Transpose:=False
gt; Sheets(quot;Quotequot;).Select
gt; Range(quot;N47:U47quot;).Select
gt;
gt; End Sub
gt;

Hi,

Yes B**:M** are merged cells

Does that complicate matters??

quot;Nigelquot; wrote:

gt; hi,
gt;
gt; could you copy the range H12:H18 and paste it into your other cells on the
gt; Quote sheet in one job lot? are the cells on quote book B**:M** merged cells?
gt;
gt; regs,
gt;
gt; Nigel
gt;
gt;
gt; quot;Alarmblokequot; wrote:
gt;
gt; gt; Hello again,
gt; gt;
gt; gt; I currently have a few macros which copy and paste data from one sheet to
gt; gt; another.
gt; gt;
gt; gt; When the run you see the sheets flicking between each other as the macro
gt; gt; selects the active sheet, copies the selected range, then selects the
gt; gt; destination sheet pastes etc etc.
gt; gt;
gt; gt; This all works as it should but is there a way of doing it in the background
gt; gt; whilst the user just sees the main input sheet and not flicking pages.
gt; gt;
gt; gt; This would seriously help with saving my eye sight
gt; gt;
gt; gt; Messy(Macro) as below
gt; gt;
gt; gt; Sub transfer()
gt; gt; '
gt; gt; ' transfer Macro
gt; gt; '
gt; gt; '
gt; gt;
gt; gt; '
gt; gt; Range(quot;H11quot;).Select
gt; gt; Selection.Copy
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;B48:M48quot;).Select
gt; gt; ActiveSheet.Paste
gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; Range(quot;H12quot;).Select
gt; gt; Selection.Copy
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;B50:M50quot;).Select
gt; gt; ActiveSheet.Paste
gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; Range(quot;H13quot;).Select
gt; gt; Selection.Copy
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;B51:M51quot;).Select
gt; gt; ActiveSheet.Paste
gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; Range(quot;H14quot;).Select
gt; gt; Selection.Copy
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;B52:M52quot;).Select
gt; gt; ActiveSheet.Paste
gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; Range(quot;H15quot;).Select
gt; gt; Selection.Copy
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;B53:M53quot;).Select
gt; gt; ActiveSheet.Paste
gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; Range(quot;H16quot;).Select
gt; gt; Selection.Copy
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;B54:M54quot;).Select
gt; gt; ActiveSheet.Paste
gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; Range(quot;H17quot;).Select
gt; gt; Selection.Copy
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;B55:M55quot;).Select
gt; gt; ActiveSheet.Paste
gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; Range(quot;H18quot;).Select
gt; gt; Selection.Copy
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;B56quot;).Select
gt; gt; Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
gt; gt; SkipBlanks _
gt; gt; :=False, Transpose:=False
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;N47:U47quot;).Select
gt; gt;
gt; gt; End Sub
gt; gt;

Should also add that the data that is being copied doesnt stay the same all
the time, this is to say I only need a snapshot of the data rather than using
=Sheet1!H12 for example which I assume would alter the data automatically if
it changes.
Sorry to complicate matters

quot;Alarmblokequot; wrote:

gt; Hello again,
gt;
gt; I currently have a few macros which copy and paste data from one sheet to
gt; another.
gt;
gt; When the run you see the sheets flicking between each other as the macro
gt; selects the active sheet, copies the selected range, then selects the
gt; destination sheet pastes etc etc.
gt;
gt; This all works as it should but is there a way of doing it in the background
gt; whilst the user just sees the main input sheet and not flicking pages.
gt;
gt; This would seriously help with saving my eye sight
gt;
gt; Messy(Macro) as below
gt;
gt; Sub transfer()
gt; '
gt; ' transfer Macro
gt; '
gt; '
gt;
gt; '
gt; Range(quot;H11quot;).Select
gt; Selection.Copy
gt; Sheets(quot;Quotequot;).Select
gt; Range(quot;B48:M48quot;).Select
gt; ActiveSheet.Paste
gt; Sheets(quot;Sheet1quot;).Select
gt; Range(quot;H12quot;).Select
gt; Selection.Copy
gt; Sheets(quot;Quotequot;).Select
gt; Range(quot;B50:M50quot;).Select
gt; ActiveSheet.Paste
gt; Sheets(quot;Sheet1quot;).Select
gt; Range(quot;H13quot;).Select
gt; Selection.Copy
gt; Sheets(quot;Quotequot;).Select
gt; Range(quot;B51:M51quot;).Select
gt; ActiveSheet.Paste
gt; Sheets(quot;Sheet1quot;).Select
gt; Range(quot;H14quot;).Select
gt; Selection.Copy
gt; Sheets(quot;Quotequot;).Select
gt; Range(quot;B52:M52quot;).Select
gt; ActiveSheet.Paste
gt; Sheets(quot;Sheet1quot;).Select
gt; Range(quot;H15quot;).Select
gt; Selection.Copy
gt; Sheets(quot;Quotequot;).Select
gt; Range(quot;B53:M53quot;).Select
gt; ActiveSheet.Paste
gt; Sheets(quot;Sheet1quot;).Select
gt; Range(quot;H16quot;).Select
gt; Selection.Copy
gt; Sheets(quot;Quotequot;).Select
gt; Range(quot;B54:M54quot;).Select
gt; ActiveSheet.Paste
gt; Sheets(quot;Sheet1quot;).Select
gt; Range(quot;H17quot;).Select
gt; Selection.Copy
gt; Sheets(quot;Quotequot;).Select
gt; Range(quot;B55:M55quot;).Select
gt; ActiveSheet.Paste
gt; Sheets(quot;Sheet1quot;).Select
gt; Range(quot;H18quot;).Select
gt; Selection.Copy
gt; Sheets(quot;Quotequot;).Select
gt; Range(quot;B56quot;).Select
gt; Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
gt; SkipBlanks _
gt; :=False, Transpose:=False
gt; Sheets(quot;Quotequot;).Select
gt; Range(quot;N47:U47quot;).Select
gt;
gt; End Sub
gt;

To avoid flickering:

Application.Screenupdating = False
Your code
Application.Screenupdating = Truehth
knut
quot;Alarmblokequot; gt; skrev i melding
...
gt; Hello again,
gt;
gt; I currently have a few macros which copy and paste data from one sheet to
gt; another.
gt;
gt; When the run you see the sheets flicking between each other as the macro
gt; selects the active sheet, copies the selected range, then selects the
gt; destination sheet pastes etc etc.
gt;
gt; This all works as it should but is there a way of doing it in the
gt; background
gt; whilst the user just sees the main input sheet and not flicking pages.
gt;
gt; This would seriously help with saving my eye sight
gt;
gt; Messy(Macro) as below
gt;
gt; Sub transfer()
gt; '
gt; ' transfer Macro
gt; '
gt; '
gt;
gt; '
gt; Range(quot;H11quot;).Select
gt; Selection.Copy
gt; Sheets(quot;Quotequot;).Select
gt; Range(quot;B48:M48quot;).Select
gt; ActiveSheet.Paste
gt; Sheets(quot;Sheet1quot;).Select
gt; Range(quot;H12quot;).Select
gt; Selection.Copy
gt; Sheets(quot;Quotequot;).Select
gt; Range(quot;B50:M50quot;).Select
gt; ActiveSheet.Paste
gt; Sheets(quot;Sheet1quot;).Select
gt; Range(quot;H13quot;).Select
gt; Selection.Copy
gt; Sheets(quot;Quotequot;).Select
gt; Range(quot;B51:M51quot;).Select
gt; ActiveSheet.Paste
gt; Sheets(quot;Sheet1quot;).Select
gt; Range(quot;H14quot;).Select
gt; Selection.Copy
gt; Sheets(quot;Quotequot;).Select
gt; Range(quot;B52:M52quot;).Select
gt; ActiveSheet.Paste
gt; Sheets(quot;Sheet1quot;).Select
gt; Range(quot;H15quot;).Select
gt; Selection.Copy
gt; Sheets(quot;Quotequot;).Select
gt; Range(quot;B53:M53quot;).Select
gt; ActiveSheet.Paste
gt; Sheets(quot;Sheet1quot;).Select
gt; Range(quot;H16quot;).Select
gt; Selection.Copy
gt; Sheets(quot;Quotequot;).Select
gt; Range(quot;B54:M54quot;).Select
gt; ActiveSheet.Paste
gt; Sheets(quot;Sheet1quot;).Select
gt; Range(quot;H17quot;).Select
gt; Selection.Copy
gt; Sheets(quot;Quotequot;).Select
gt; Range(quot;B55:M55quot;).Select
gt; ActiveSheet.Paste
gt; Sheets(quot;Sheet1quot;).Select
gt; Range(quot;H18quot;).Select
gt; Selection.Copy
gt; Sheets(quot;Quotequot;).Select
gt; Range(quot;B56quot;).Select
gt; Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
gt; SkipBlanks _
gt; :=False, Transpose:=False
gt; Sheets(quot;Quotequot;).Select
gt; Range(quot;N47:U47quot;).Select
gt;
gt; End Sub
gt;
Amazing !!!! My pulsating eyes thank you immensly, just what the option ordered
quot;DS NTEquot; wrote:

gt; To avoid flickering:
gt;
gt; Application.Screenupdating = False
gt; Your code
gt; Application.Screenupdating = True
gt;
gt;
gt; hth
gt; knut
gt; quot;Alarmblokequot; gt; skrev i melding
gt; ...
gt; gt; Hello again,
gt; gt;
gt; gt; I currently have a few macros which copy and paste data from one sheet to
gt; gt; another.
gt; gt;
gt; gt; When the run you see the sheets flicking between each other as the macro
gt; gt; selects the active sheet, copies the selected range, then selects the
gt; gt; destination sheet pastes etc etc.
gt; gt;
gt; gt; This all works as it should but is there a way of doing it in the
gt; gt; background
gt; gt; whilst the user just sees the main input sheet and not flicking pages.
gt; gt;
gt; gt; This would seriously help with saving my eye sight
gt; gt;
gt; gt; Messy(Macro) as below
gt; gt;
gt; gt; Sub transfer()
gt; gt; '
gt; gt; ' transfer Macro
gt; gt; '
gt; gt; '
gt; gt;
gt; gt; '
gt; gt; Range(quot;H11quot;).Select
gt; gt; Selection.Copy
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;B48:M48quot;).Select
gt; gt; ActiveSheet.Paste
gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; Range(quot;H12quot;).Select
gt; gt; Selection.Copy
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;B50:M50quot;).Select
gt; gt; ActiveSheet.Paste
gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; Range(quot;H13quot;).Select
gt; gt; Selection.Copy
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;B51:M51quot;).Select
gt; gt; ActiveSheet.Paste
gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; Range(quot;H14quot;).Select
gt; gt; Selection.Copy
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;B52:M52quot;).Select
gt; gt; ActiveSheet.Paste
gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; Range(quot;H15quot;).Select
gt; gt; Selection.Copy
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;B53:M53quot;).Select
gt; gt; ActiveSheet.Paste
gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; Range(quot;H16quot;).Select
gt; gt; Selection.Copy
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;B54:M54quot;).Select
gt; gt; ActiveSheet.Paste
gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; Range(quot;H17quot;).Select
gt; gt; Selection.Copy
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;B55:M55quot;).Select
gt; gt; ActiveSheet.Paste
gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; Range(quot;H18quot;).Select
gt; gt; Selection.Copy
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;B56quot;).Select
gt; gt; Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
gt; gt; SkipBlanks _
gt; gt; :=False, Transpose:=False
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;N47:U47quot;).Select
gt; gt;
gt; gt; End Sub
gt; gt;
gt;
gt;
gt;

Hi,

You could use this:

Sub Transfer()
Application.ScreenUpdating = False

Sheets(quot;Quotequot;).Select

Range(quot;H11quot;).Select
Ranger1 = ActiveCell.Value
Range(quot;H12quot;).Select
Ranger2 = ActiveCell.Value
Range(quot;H13quot;).Select
Ranger3 = ActiveCell.Value
Range(quot;H14quot;).Select
Ranger4 = ActiveCell.Value
Range(quot;H15quot;).Select
Ranger5 = ActiveCell.Value
Range(quot;H16quot;).Select
Ranger6 = ActiveCell.Value
Range(quot;H17quot;).Select
Ranger7 = ActiveCell.Value
Range(quot;H18quot;).Select
Ranger8 = ActiveCell.Value

Range(quot;Sheet1!B48quot;).Value = Ranger1
Range(quot;Sheet1!B50quot;).Value = Ranger2
Range(quot;Sheet1!B51quot;).Value = Ranger3
Range(quot;Sheet1!B52quot;).Value = Ranger4
Range(quot;Sheet1!B53quot;).Value = Ranger5
Range(quot;Sheet1!B54quot;).Value = Ranger6
Range(quot;Sheet1!B55quot;).Value = Ranger7
Range(quot;Sheet1!B56quot;).Value = Ranger8

Sheets(quot;Quotequot;).Select
Range(quot;N47:U47quot;).Select

Application.ScreenUpdating = True

End Sub

This nests all of the required information from quot;Quotequot; and places it into
quot;Sheet1quot;.
there is no need for pastespecials as it is placing the data Value only and
not a path.

the screen does not change so the info is transfers quickly in the
background in 1 movement rather than 9.

Hope it helps.

Nigel
quot;Alarmblokequot; wrote:

gt; Amazing !!!! My pulsating eyes thank you immensly, just what the option ordered
gt;
gt;
gt;
gt; quot;DS NTEquot; wrote:
gt;
gt; gt; To avoid flickering:
gt; gt;
gt; gt; Application.Screenupdating = False
gt; gt; Your code
gt; gt; Application.Screenupdating = True
gt; gt;
gt; gt;
gt; gt; hth
gt; gt; knut
gt; gt; quot;Alarmblokequot; gt; skrev i melding
gt; gt; ...
gt; gt; gt; Hello again,
gt; gt; gt;
gt; gt; gt; I currently have a few macros which copy and paste data from one sheet to
gt; gt; gt; another.
gt; gt; gt;
gt; gt; gt; When the run you see the sheets flicking between each other as the macro
gt; gt; gt; selects the active sheet, copies the selected range, then selects the
gt; gt; gt; destination sheet pastes etc etc.
gt; gt; gt;
gt; gt; gt; This all works as it should but is there a way of doing it in the
gt; gt; gt; background
gt; gt; gt; whilst the user just sees the main input sheet and not flicking pages.
gt; gt; gt;
gt; gt; gt; This would seriously help with saving my eye sight
gt; gt; gt;
gt; gt; gt; Messy(Macro) as below
gt; gt; gt;
gt; gt; gt; Sub transfer()
gt; gt; gt; '
gt; gt; gt; ' transfer Macro
gt; gt; gt; '
gt; gt; gt; '
gt; gt; gt;
gt; gt; gt; '
gt; gt; gt; Range(quot;H11quot;).Select
gt; gt; gt; Selection.Copy
gt; gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; gt; Range(quot;B48:M48quot;).Select
gt; gt; gt; ActiveSheet.Paste
gt; gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; gt; Range(quot;H12quot;).Select
gt; gt; gt; Selection.Copy
gt; gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; gt; Range(quot;B50:M50quot;).Select
gt; gt; gt; ActiveSheet.Paste
gt; gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; gt; Range(quot;H13quot;).Select
gt; gt; gt; Selection.Copy
gt; gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; gt; Range(quot;B51:M51quot;).Select
gt; gt; gt; ActiveSheet.Paste
gt; gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; gt; Range(quot;H14quot;).Select
gt; gt; gt; Selection.Copy
gt; gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; gt; Range(quot;B52:M52quot;).Select
gt; gt; gt; ActiveSheet.Paste
gt; gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; gt; Range(quot;H15quot;).Select
gt; gt; gt; Selection.Copy
gt; gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; gt; Range(quot;B53:M53quot;).Select
gt; gt; gt; ActiveSheet.Paste
gt; gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; gt; Range(quot;H16quot;).Select
gt; gt; gt; Selection.Copy
gt; gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; gt; Range(quot;B54:M54quot;).Select
gt; gt; gt; ActiveSheet.Paste
gt; gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; gt; Range(quot;H17quot;).Select
gt; gt; gt; Selection.Copy
gt; gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; gt; Range(quot;B55:M55quot;).Select
gt; gt; gt; ActiveSheet.Paste
gt; gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; gt; Range(quot;H18quot;).Select
gt; gt; gt; Selection.Copy
gt; gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; gt; Range(quot;B56quot;).Select
gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
gt; gt; gt; SkipBlanks _
gt; gt; gt; :=False, Transpose:=False
gt; gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; gt; Range(quot;N47:U47quot;).Select
gt; gt; gt;
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;

Hello DS NTE,
How can I make your code:
Application.Screenupdating = False
Your code
Application.Screenupdating = True

work with:
Private Sub Worksheet_Calculate()
Const MyCells As String = quot;c7,b48,b49,b50,b51,b54,b55,b56quot; 'adjust the
range to suit
On Error GoTo stoppit Application.EnableEvents = False For Each cell In Me.Range(MyCells) With cell If .Value = quot;quot; Then .EntireRow.Hidden = True Else .EntireRow.Hidden = False End If
End With
Next
stoppit:
Application.EnableEvents = True
End Sub

So it doesnt blow up????
Thank you.
TG
quot;DS NTEquot; wrote:

gt; To avoid flickering:
gt;
gt; Application.Screenupdating = False
gt; Your code
gt; Application.Screenupdating = True
gt;
gt;
gt; hth
gt; knut
gt; quot;Alarmblokequot; gt; skrev i melding
gt; ...
gt; gt; Hello again,
gt; gt;
gt; gt; I currently have a few macros which copy and paste data from one sheet to
gt; gt; another.
gt; gt;
gt; gt; When the run you see the sheets flicking between each other as the macro
gt; gt; selects the active sheet, copies the selected range, then selects the
gt; gt; destination sheet pastes etc etc.
gt; gt;
gt; gt; This all works as it should but is there a way of doing it in the
gt; gt; background
gt; gt; whilst the user just sees the main input sheet and not flicking pages.
gt; gt;
gt; gt; This would seriously help with saving my eye sight
gt; gt;
gt; gt; Messy(Macro) as below
gt; gt;
gt; gt; Sub transfer()
gt; gt; '
gt; gt; ' transfer Macro
gt; gt; '
gt; gt; '
gt; gt;
gt; gt; '
gt; gt; Range(quot;H11quot;).Select
gt; gt; Selection.Copy
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;B48:M48quot;).Select
gt; gt; ActiveSheet.Paste
gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; Range(quot;H12quot;).Select
gt; gt; Selection.Copy
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;B50:M50quot;).Select
gt; gt; ActiveSheet.Paste
gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; Range(quot;H13quot;).Select
gt; gt; Selection.Copy
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;B51:M51quot;).Select
gt; gt; ActiveSheet.Paste
gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; Range(quot;H14quot;).Select
gt; gt; Selection.Copy
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;B52:M52quot;).Select
gt; gt; ActiveSheet.Paste
gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; Range(quot;H15quot;).Select
gt; gt; Selection.Copy
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;B53:M53quot;).Select
gt; gt; ActiveSheet.Paste
gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; Range(quot;H16quot;).Select
gt; gt; Selection.Copy
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;B54:M54quot;).Select
gt; gt; ActiveSheet.Paste
gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; Range(quot;H17quot;).Select
gt; gt; Selection.Copy
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;B55:M55quot;).Select
gt; gt; ActiveSheet.Paste
gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; Range(quot;H18quot;).Select
gt; gt; Selection.Copy
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;B56quot;).Select
gt; gt; Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
gt; gt; SkipBlanks _
gt; gt; :=False, Transpose:=False
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;N47:U47quot;).Select
gt; gt;
gt; gt; End Sub
gt; gt;
gt;
gt;
gt;

Hello DS NTE,
How can I make your code:
Application.Screenupdating = False
Your code
Application.Screenupdating = True

work with:
Private Sub Worksheet_Calculate()
Const MyCells As String = quot;c7,b48,b49,b50,b51,b54,b55,b56quot; 'adjust the
range to suit
On Error GoTo stoppit Application.EnableEvents = False For Each cell In Me.Range(MyCells) With cell If .Value = quot;quot; Then .EntireRow.Hidden = True Else .EntireRow.Hidden = False End If
End With
Next
stoppit:
Application.EnableEvents = True
End Sub

So it doesnt blow up????
Thank you.
TGquot;DS NTEquot; wrote:

gt; To avoid flickering:
gt;
gt; Application.Screenupdating = False
gt; Your code
gt; Application.Screenupdating = True
gt;
gt;
gt; hth
gt; knut
gt; quot;Alarmblokequot; gt; skrev i melding
gt; ...
gt; gt; Hello again,
gt; gt;
gt; gt; I currently have a few macros which copy and paste data from one sheet to
gt; gt; another.
gt; gt;
gt; gt; When the run you see the sheets flicking between each other as the macro
gt; gt; selects the active sheet, copies the selected range, then selects the
gt; gt; destination sheet pastes etc etc.
gt; gt;
gt; gt; This all works as it should but is there a way of doing it in the
gt; gt; background
gt; gt; whilst the user just sees the main input sheet and not flicking pages.
gt; gt;
gt; gt; This would seriously help with saving my eye sight
gt; gt;
gt; gt; Messy(Macro) as below
gt; gt;
gt; gt; Sub transfer()
gt; gt; '
gt; gt; ' transfer Macro
gt; gt; '
gt; gt; '
gt; gt;
gt; gt; '
gt; gt; Range(quot;H11quot;).Select
gt; gt; Selection.Copy
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;B48:M48quot;).Select
gt; gt; ActiveSheet.Paste
gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; Range(quot;H12quot;).Select
gt; gt; Selection.Copy
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;B50:M50quot;).Select
gt; gt; ActiveSheet.Paste
gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; Range(quot;H13quot;).Select
gt; gt; Selection.Copy
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;B51:M51quot;).Select
gt; gt; ActiveSheet.Paste
gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; Range(quot;H14quot;).Select
gt; gt; Selection.Copy
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;B52:M52quot;).Select
gt; gt; ActiveSheet.Paste
gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; Range(quot;H15quot;).Select
gt; gt; Selection.Copy
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;B53:M53quot;).Select
gt; gt; ActiveSheet.Paste
gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; Range(quot;H16quot;).Select
gt; gt; Selection.Copy
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;B54:M54quot;).Select
gt; gt; ActiveSheet.Paste
gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; Range(quot;H17quot;).Select
gt; gt; Selection.Copy
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;B55:M55quot;).Select
gt; gt; ActiveSheet.Paste
gt; gt; Sheets(quot;Sheet1quot;).Select
gt; gt; Range(quot;H18quot;).Select
gt; gt; Selection.Copy
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;B56quot;).Select
gt; gt; Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
gt; gt; SkipBlanks _
gt; gt; :=False, Transpose:=False
gt; gt; Sheets(quot;Quotequot;).Select
gt; gt; Range(quot;N47:U47quot;).Select
gt; gt;
gt; gt; End Sub
gt; gt;
gt;
gt;
gt;

Why would you want the screen to not update?

All you're doing is hiding and unhiding rows based on content of some cells.

No jumping around happening.

If you do add the two lines you are saying Excel crashes or the code blows
up?

Doesn't for me when I add them as such. But they are not necessary IMO.

Private Sub Worksheet_Calculate()
Const MyCells As String = quot;c7,b48,b49,b50,b51,b54,b55,b56quot; 'adjust the
'range to suit
On Error GoTo stoppit With Application .EnableEvents = False .ScreenUpdating = False End With For Each cell In Me.Range(MyCells) With cell If .Value = quot;quot; Then .EntireRow.Hidden = True Else .EntireRow.Hidden = False End If
End With
Next
stoppit: With Application .EnableEvents = True .ScreenUpdating = True End With
End SubGord Dibben MS Excel MVPOn Thu, 8 Jan 2009 11:57:00 -0800, TG gt; wrote:

gt;Hello DS NTE,
gt;How can I make your code:
gt;Application.Screenupdating = False
gt;Your code
gt;Application.Screenupdating = True
gt;
gt;work with:
gt;Private Sub Worksheet_Calculate()
gt;Const MyCells As String = quot;c7,b48,b49,b50,b51,b54,b55,b56quot; 'adjust the
gt;range to suit
gt;On Error GoTo stoppit
gt; Application.EnableEvents = False
gt; For Each cell In Me.Range(MyCells)
gt; With cell
gt; If .Value = quot;quot; Then
gt; .EntireRow.Hidden = True
gt; Else
gt; .EntireRow.Hidden = False
gt; End If
gt;End With
gt;Next
gt;stoppit:
gt;Application.EnableEvents = True
gt;End Sub
gt;
gt;So it doesnt blow up????
gt;Thank you.
gt;TG
gt;
gt;
gt;
gt;quot;DS NTEquot; wrote:
gt;
gt;gt; To avoid flickering:
gt;gt;
gt;gt; Application.Screenupdating = False
gt;gt; Your code
gt;gt; Application.Screenupdating = True
gt;gt;
gt;gt;
gt;gt; hth
gt;gt; knut
gt;gt; quot;Alarmblokequot; gt; skrev i melding
gt;gt; ...
gt;gt; gt; Hello again,
gt;gt; gt;
gt;gt; gt; I currently have a few macros which copy and paste data from one sheet to
gt;gt; gt; another.
gt;gt; gt;
gt;gt; gt; When the run you see the sheets flicking between each other as the macro
gt;gt; gt; selects the active sheet, copies the selected range, then selects the
gt;gt; gt; destination sheet pastes etc etc.
gt;gt; gt;
gt;gt; gt; This all works as it should but is there a way of doing it in the
gt;gt; gt; background
gt;gt; gt; whilst the user just sees the main input sheet and not flicking pages.
gt;gt; gt;
gt;gt; gt; This would seriously help with saving my eye sight
gt;gt; gt;
gt;gt; gt; Messy(Macro) as below
gt;gt; gt;
gt;gt; gt; Sub transfer()
gt;gt; gt; '
gt;gt; gt; ' transfer Macro
gt;gt; gt; '
gt;gt; gt; '
gt;gt; gt;
gt;gt; gt; '
gt;gt; gt; Range(quot;H11quot;).Select
gt;gt; gt; Selection.Copy
gt;gt; gt; Sheets(quot;Quotequot;).Select
gt;gt; gt; Range(quot;B48:M48quot;).Select
gt;gt; gt; ActiveSheet.Paste
gt;gt; gt; Sheets(quot;Sheet1quot;).Select
gt;gt; gt; Range(quot;H12quot;).Select
gt;gt; gt; Selection.Copy
gt;gt; gt; Sheets(quot;Quotequot;).Select
gt;gt; gt; Range(quot;B50:M50quot;).Select
gt;gt; gt; ActiveSheet.Paste
gt;gt; gt; Sheets(quot;Sheet1quot;).Select
gt;gt; gt; Range(quot;H13quot;).Select
gt;gt; gt; Selection.Copy
gt;gt; gt; Sheets(quot;Quotequot;).Select
gt;gt; gt; Range(quot;B51:M51quot;).Select
gt;gt; gt; ActiveSheet.Paste
gt;gt; gt; Sheets(quot;Sheet1quot;).Select
gt;gt; gt; Range(quot;H14quot;).Select
gt;gt; gt; Selection.Copy
gt;gt; gt; Sheets(quot;Quotequot;).Select
gt;gt; gt; Range(quot;B52:M52quot;).Select
gt;gt; gt; ActiveSheet.Paste
gt;gt; gt; Sheets(quot;Sheet1quot;).Select
gt;gt; gt; Range(quot;H15quot;).Select
gt;gt; gt; Selection.Copy
gt;gt; gt; Sheets(quot;Quotequot;).Select
gt;gt; gt; Range(quot;B53:M53quot;).Select
gt;gt; gt; ActiveSheet.Paste
gt;gt; gt; Sheets(quot;Sheet1quot;).Select
gt;gt; gt; Range(quot;H16quot;).Select
gt;gt; gt; Selection.Copy
gt;gt; gt; Sheets(quot;Quotequot;).Select
gt;gt; gt; Range(quot;B54:M54quot;).Select
gt;gt; gt; ActiveSheet.Paste
gt;gt; gt; Sheets(quot;Sheet1quot;).Select
gt;gt; gt; Range(quot;H17quot;).Select
gt;gt; gt; Selection.Copy
gt;gt; gt; Sheets(quot;Quotequot;).Select
gt;gt; gt; Range(quot;B55:M55quot;).Select
gt;gt; gt; ActiveSheet.Paste
gt;gt; gt; Sheets(quot;Sheet1quot;).Select
gt;gt; gt; Range(quot;H18quot;).Select
gt;gt; gt; Selection.Copy
gt;gt; gt; Sheets(quot;Quotequot;).Select
gt;gt; gt; Range(quot;B56quot;).Select
gt;gt; gt; Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
gt;gt; gt; SkipBlanks _
gt;gt; gt; :=False, Transpose:=False
gt;gt; gt; Sheets(quot;Quotequot;).Select
gt;gt; gt; Range(quot;N47:U47quot;).Select
gt;gt; gt;
gt;gt; gt; End Sub
gt;gt; gt;
gt;gt;
gt;gt;
gt;gt;