ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Copy Paste Macro not Pasting Values when Blank (https://www.excelbanter.com/excel-programming/417101-copy-paste-macro-not-pasting-values-when-blank.html)

Corey
Copy Paste Macro not Pasting Values when Blank
 
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


--
Corey ....
The Silliest Question is generally
the one i forgot to ask.



OssieMac
Copy Paste Macro not Pasting Values when Blank
 
Untested but try selecting the worksheet after making it visible. It might
not be the selected worksheet.

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

--
Regards,

OssieMac


"Corey" wrote:

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


--
Corey ....
The Silliest Question is generally
the one i forgot to ask.




Corey
Copy Paste Macro not Pasting Values when Blank
 
Thanks for the reply.

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

"OssieMac" wrote in message
...
Untested but try selecting the worksheet after making it visible. It might
not be the selected worksheet.

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

--
Regards,

OssieMac


"Corey" wrote:

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


--
Corey ....
The Silliest Question is generally
the one i forgot to ask.

OssieMac
Copy Paste Macro not Pasting Values when Blank
 
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.

--
Regards,

OssieMac


"Corey" wrote:

Thanks for the reply.

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

"OssieMac" wrote in message
...
Untested but try selecting the worksheet after making it visible. It might
not be the selected worksheet.

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

--
Regards,

OssieMac


"Corey" wrote:

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


--
Corey ....
The Silliest Question is generally
the one i forgot to ask.


All times are GMT +1. The time now is 09:18 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com