• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro to copy and paste column values

T

theath

New Member
I have a spreadsheet that must have values from three seperate columns copied and pasted into three other columns. The columns being copied from have their data generated from formulas. I need to paste the values only into the new columns. The original values and formulas must be left in the columns being copied from.


Unfortunately the values in the original columns are being erased and they are not being copied over to the new columns in the script that I am working with. Here is what I have so far.


Range("Table1[Cumulative % Complete]").Select

Selection.Copy

Range("Table1[Actual % Complete]").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False


Range("Table1[Revenue Realized]").Select

Application.CutCopyMode = xlCopy

Selection.Copy

Range("Table1[Realized Revenue]").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False


Range("Table1[Cumulative Revenue]").Select

Application.CutCopyMode = xlCopy

Selection.Copy

Range("Table1[Cumulative Revenues]").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False
 
Trimming your code:

[pre]
Code: 
'Didn't need all those Selection methods, and we're
'using several of the PasteSpecial defaults

Range("Table1[Cumulative % Complete]").Copy
Range("Table1[Actual % Complete]").PasteSpecial Paste:=xlPasteValues

Range("Table1[Revenue Realized]").Copy
Range("Table1[Realized Revenue]").PasteSpecial Paste:=xlPasteValues

Range("Table1[Cumulative Revenue]").Copy
Range("Table1[Cumulative Revenues]").PasteSpecial Paste:=xlPasteValues
[/pre]
Are the formulas in Table 1 dependent on the areas being pasted into? If so, you may need to set calculation mode to manual before copying the data.
 
This is still doing the same behaviour.


The copy and paste from "Revenue Realized" to "Realized Revenue" deletes the the data out of Revenue Realized and does not update Realized Revenue. If I comment out the other copy and pastes, this works fine. If the other copy and pastes are not commented out it does not work though the other two work fine.


Thank you for any help!!
 
Hi ,


Are any of your ranges overlapping ?


Can you check the range addresses for each of your named ranges ?


Narayan
 
None of them are overlapping. The strange thing is that I can manually copy and paste each of these ranges without a problem. It is only when I run the script with all three that there is an issue. I spent a few hours last night trying to figure this one out and it has me completely stumped!
 
Sub cmdButton6_OnClick()

Two

Three

One


End Sub


Sub One()

Range("Table1[Cumulative % Complete]").Copy

Range("Table1[Actual % Complete]").PasteSpecial Paste:=xlPasteValues

End Sub

Sub Two()

Range("Table1[Revenue Realized]").Copy

Range("Table1[Realized Revenue]").PasteSpecial Paste:=xlPasteValues

End Sub


Sub Three()

Range("Table1[Cumulative Revenue]").Copy

Range("Table1[Cumulative Revenues]").PasteSpecial Paste:=xlPasteValues


End Sub
 
Hi ,


You are executing the routines TWO , THREE and then ONE ; is this order critical ? Have you tried changing this order and seeing whether the problem remains the same ?


Narayan
 
I have tried changing the order. I have commented out those routines. I have done all kinds of things. I can do this in any order manually. When I run the script, I have the issues. The order is different in the script above because I was changing it around to see if it helped the issue. I just can't seem to figure out why I can manually copy and paste the data, but the script does not work.
 
Hi ,


Can you try just two more things ?


1. Replace the range names with their addresses , if that is possible , and try the script.


2. Step through the script ; you can do this by positioning your cursor in the routine , and pressing F8 ; with each press of the F8 key , the next step in the program will be executed. Each time you press the F8 key and go to the next step , you can view your Excel worksheet to see what is happening.


Narayan
 
I just tried it on my backup spreadsheet. Go figure. It worked like a charm. I updated that copy with the changes I made since last saving to it and things are OK now. I have no idea what was going on with the last one, but it seems to work now.


Thank you for your help! It is greatly appreciated!
 
You must log in or register to reply here.
Back
Top