Macro recorded to copy and paste only values
Closed
jutulu
Posts
34
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 21, 2015
-
Mar 10, 2014 at 06:24 PM
jutulu - Mar 12, 2014 at 09:00 PM
jutulu - Mar 12, 2014 at 09:00 PM
Related:
- Macro recorded to copy and paste only values
- Spell number in excel without macro - Guide
- How to send recorded audio in messenger - Guide
- Copy and paste fonts - Guide
- How to download recording from messenger - Guide
- How to paste photo in resume - Guide
4 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Mar 11, 2014 at 01:32 AM
Mar 11, 2014 at 01:32 AM
you can manipulate without selection for examle
range("a1").copy range("B1"
so "selection" here
if you are new to vba activate the sheet once. later as you learn more you can even avoid activating sheet by using with---end with
in copying certain arguments are defaults and if only defaults are used need not use all the arguments. for e.g. in copying (above) operation,skilp blanks, translpose etc need not be used if they are default values
when you copy one cell or range
and again copy another range, previous copying is removed
but at the end you can add (choice)
application.cutcopymode=false
not every time.
you are in the right path and you will learn to improve the macro
now try again and write the macro and TEST the macro.
range("a1").copy range("B1"
so "selection" here
if you are new to vba activate the sheet once. later as you learn more you can even avoid activating sheet by using with---end with
in copying certain arguments are defaults and if only defaults are used need not use all the arguments. for e.g. in copying (above) operation,skilp blanks, translpose etc need not be used if they are default values
when you copy one cell or range
and again copy another range, previous copying is removed
but at the end you can add (choice)
application.cutcopymode=false
not every time.
you are in the right path and you will learn to improve the macro
now try again and write the macro and TEST the macro.
jutulu
Posts
34
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 21, 2015
2
Mar 11, 2014 at 03:15 PM
Mar 11, 2014 at 03:15 PM
Thank you for your encouraging words and the advice, but I don't have the confidence to actually complete and successfully getting it to work. Ie, for the paste special it says "Operation:=xlNone, SkipBlanks _".....what does it mean xINone, SkipBlanks or the "Offset(0, 14).Range("A1")"? I tried deleting SkipBlanks but it only make it worst and really runn out of ideas now. The above code must be close, but if you could help me to complete it really could use your help with it. The only reason I got the above code is because I used the Record option, but it is not as easy for more complicated macros like the one I require.
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Mar 12, 2014 at 12:56 AM
Mar 12, 2014 at 12:56 AM
Your doubts on PASTESPECIAL in a macro
In the vb editor click "help" in menu bar
Enter PASTESPECIAL
Choose rangepastespecial option
You will get the details
For .e.g. operation means do you want to add the existing value in the paste cell
e.g.
Range("a1") has entry 2 (source to be copied)
Range("G1") has etry 4 (destination to be pasted)
If you use
Operation:=2 this mean when a1 is copied to G1 in G1 it will be 4(what is in G1) is added to A1(2) and final G1 value will be 6
In most cases you do not want to any of these operation and so
Xlnone
Operation:=xlnone is default and even if you do not have this code it is automatically taken as xl none
Similarly other arguments.
Just type
range("A1").copy
range("G1").pastespecial
this is enough
USE PROFUSELY HELP
2. to modify your macro it is difficult to find what you want to do.
SO POST A SMALL AMOUNT OF DATA
AND EXPLAIN WHAT YOU WANT TO DO
MY PERSONAL PREDILECTION, KEEP THE WORKBOOK NAME AND WORKHEET NAME AS SMALL AS POSSIBLE
when you activate a workbook (ro window) you have to activate teh sheet also
Otherwise every time you have to select the cell. use the req1uired cell address
There is no provision in this newsgroup to attach a file. You can go to
Speedyshare.com
And upload your file and send me the url for the file to be downloaded.
and then explain clearly what you want to do.
In the vb editor click "help" in menu bar
Enter PASTESPECIAL
Choose rangepastespecial option
You will get the details
For .e.g. operation means do you want to add the existing value in the paste cell
e.g.
Range("a1") has entry 2 (source to be copied)
Range("G1") has etry 4 (destination to be pasted)
If you use
Operation:=2 this mean when a1 is copied to G1 in G1 it will be 4(what is in G1) is added to A1(2) and final G1 value will be 6
In most cases you do not want to any of these operation and so
Xlnone
Operation:=xlnone is default and even if you do not have this code it is automatically taken as xl none
Similarly other arguments.
Just type
range("A1").copy
range("G1").pastespecial
this is enough
USE PROFUSELY HELP
2. to modify your macro it is difficult to find what you want to do.
SO POST A SMALL AMOUNT OF DATA
AND EXPLAIN WHAT YOU WANT TO DO
MY PERSONAL PREDILECTION, KEEP THE WORKBOOK NAME AND WORKHEET NAME AS SMALL AS POSSIBLE
when you activate a workbook (ro window) you have to activate teh sheet also
Otherwise every time you have to select the cell. use the req1uired cell address
There is no provision in this newsgroup to attach a file. You can go to
Speedyshare.com
And upload your file and send me the url for the file to be downloaded.
and then explain clearly what you want to do.
