Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Copy Paste Macro not Pasting Values when Blank
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 276
Default 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.


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,510
Default 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.



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 276
Default 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.





Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,510
Default 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.






Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro - Copy/Paste Special to Next Blank Line lajohn63 New Users to Excel 2 August 27th 12 09:27 PM
Macro for copy and paste values excelnerd Excel Discussion (Misc queries) 3 March 8th 08 06:51 PM
Need macro to check if cell is not blank & previous cell is blank, copy information from row above & paste JenIT Excel Programming 4 April 12th 07 08:56 PM
using macro to copy and paste filtered results, what if blank? priceyindevon Excel Worksheet Functions 2 December 14th 06 10:09 AM
Macro copy and paste = blank worksheet efface Excel Discussion (Misc queries) 1 April 27th 06 09:52 PM


All times are GMT +1. The time now is 02:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement