Copy without Select

[Jaamie]

The following code works but formats change and that cause me problems

Workbooks(MacroBook$).Worksheets("Control").Range("TranB4ChgA").Value = Workbooks(MacroBook$).Worksheets("Control").Range("TranNowA").Value

I'd like to copy Values and keep Number formats. I tried the following but I get Error 13 Type Mismatch. I tried Value instead of Value2 first but same result. How can I get the desired result?

 Workbooks(MacroBook$).Worksheets("Control").Range("TranB4ChgA").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
          xlNone, SkipBlanks:=False, Transpose:=False = Workbooks(MacroBook$).Worksheets("Control").Range("TranNowA").Value2

 

[MARK858]

Maybe...

Workbooks(MacroBook$).Worksheets("Control").Range("TranNowA").Copy
Workbooks(MacroBook$).Worksheets("Control").Range("TranB4ChgA").PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = False

 

[Jaamie]

Thank you Mark858. I had already tried that and it does work, however the screen flicks over to the "Control" sheet and Back to the Activesheet even with Screenupdating - False. I was trying to avoid that if possible.

 

[MARK858]

It should not flick over to a different sheet at all unless you have added either Select or Activate to the code somewhere.

 

[Jaamie]

For some reason it does. I put a stop on the next instruction Here is the code. 2 identical copies. The code in my first Post above doesn't flicker but results in format issues??

      Workbooks(MacroBook$).Worksheets("Control").Range("TranNowA").Copy
      Workbooks(MacroBook$).Worksheets("Control").Range("TranB4ChgA").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
          xlNone, SkipBlanks:=False, Transpose:=False
      Workbooks(MacroBook$).Worksheets("Control").Range("TranNowB").Copy
      Workbooks(MacroBook$).Worksheets("Control").Range("TranB4ChgB").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
          xlNone, SkipBlanks:=False, Transpose:=False
      Application.CutCopyMode = False

 

[MARK858]

Are you sure that it is the Control sheet that appears in the flicker?

 

[Jaamie]

Yes. The Control Sheet is Yellow and easy to spot. If I step thru, I don't see it. Also, it is not the CutCopyMode statement.

There are 2 copy statements. I tried them one at a time and still see the Flicker??

If I can't figure it out, I;ll have to live with the Flicker. The non-flicker alternative causes me more trouble.

Thank you for your help with this.