Macro recorded to copy and paste only values

[Closed]
Report
Posts
34
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 21, 2015
-
 jutulu -
Hi,

This is the first time I use this site and hopefully will get some help with this. Basically, I have a basic workbook which is populated everyday and this workbook, everyday is copied (only the values) onto an annual bigger workbook. The headings (they are not copied) are the same, but the column number differ from each other, which probably doesn't make it any easier. To automate it I just recorded a macro (choose relative reference) and started copying and pasting the values as normal, and when I finished I stopped the macro....I thought that it was going to be just that easy, but how wrong! When I run it, the values go anywhere in the workbook. I will be soooo grateful if somebody can help me with this.

Sub Macro8()
'
' Macro8 Macro
'
' Keyboard Shortcut: Ctrl+z
'
Windows("Traffic sheet 2014.Template.desktop.new.270214.MACRO.xlsx").Activate
ActiveCell.Range("A1:A37").Select
Selection.Copy
Windows("Traffic sheet.REPORTS.2014.MACRO ENABLED.xlsm").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Traffic sheet 2014.Template.desktop.new.270214.MACRO.xlsx").Activate
ActiveCell.Offset(0, 23).Range("A1:B37").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Traffic sheet.REPORTS.2014.MACRO ENABLED.xlsm").Activate
ActiveCell.Offset(0, 28).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Traffic sheet 2014.Template.desktop.new.270214.MACRO.xlsx").Activate
ActiveCell.Offset(0, -22).Range("A1:I37").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Traffic sheet.REPORTS.2014.MACRO ENABLED.xlsm").Activate
ActiveCell.Offset(0, 2).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Traffic sheet 2014.Template.desktop.new.270214.MACRO.xlsx").Activate
ActiveCell.Offset(0, 12).Range("A1:E38").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Traffic sheet.REPORTS.2014.MACRO ENABLED.xlsm").Activate
ActiveCell.Offset(0, 14).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Traffic sheet 2014.Template.desktop.new.270214.MACRO.xlsx").Activate
ActiveCell.Offset(0, 6).Range("A1:B38").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Traffic sheet.REPORTS.2014.MACRO ENABLED.xlsm").Activate
ActiveCell.Offset(0, 6).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Traffic sheet 2014.Template.desktop.new.270214.MACRO.xlsx").Activate
ActiveCell.Offset(0, 6).Range("A1:G38").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Traffic sheet.REPORTS.2014.MACRO ENABLED.xlsm").Activate
ActiveCell.Offset(0, 7).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

4 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
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.
Posts
34
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 21, 2015
2
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.
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
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.
Many Thanks Venkat. Still haven't have a chance to go through it and try to work it out, but will do and let you know. You are great!