Copy Paste Macro not Pasting Values when Blank

C

Corey

I recorded a Macro to copy values(only) from 1 sheet to another.
It all works great, except for one range of cells(one with Arrow).

Why ? And how do i fix it?
The rest is Ok.

Code Recorded:
Sub ReplaceOriginalValues()
Sheets("OriginalValues").Visible = True
Range("B9:B48").Select
Selection.Copy
Sheets("CostingSheet").Select
Range("B9:B48").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False ,<=== Not Pasting Correctly
Sheets("OriginalValues").Select
Range("AG13:AG23").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CostingSheet").Select
Range("AG13:AG23").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Sheets("OriginalValues").Select
Range("AJ9:AK48").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CostingSheet").Select
Range("AJ9:AK48").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Sheets("OriginalValues").Select
Range("AG26:AG28").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CostingSheet").Select
Range("AG26:AG28").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Sheets("OriginalValues").Select
Range("AG31").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CostingSheet").Select
Range("AG31").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Sheets("OriginalValues").Select
Range("AG34:AG48").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CostingSheet").Select
Range("AG34:AG48").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Sheets("OriginalValues").Select
Range("AL42").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CostingSheet").Select
Range("AL42").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Sheets("OriginalValues").Select
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Visible = False
Sheets("CostingSheet").Select
Range("B3").Select
End Sub
 
O

OssieMac

Untested but try selecting the worksheet after making it visible. It might
not be the selected worksheet.

Sheets("OriginalValues").Visible = True
Sheets("OriginalValues").Select
 
C

Corey

Thanks for the reply.

I change the PasteSpecisl to simply Paste and it seem to work now.
 
O

OssieMac

Hi Corey,

The way you have your code written, the range to copy will be whatever is
the active sheet and if you need to make the sheet visible first then it will
not be the active sheet and therefore you need to select it before selecting
the range to copy.

I have now tested this and although the sheet becomes the active sheet when
unhidden in the interactive mode, it does not record this in the macro and
you need to edit the macro and insert the line of code.

If you believe that it is working now then that is probably because the
sheet is not hidden and is the active sheet when you start the code but if
you hide the sheet again you will find that your code does not work.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top