Solved

Having Trouble with simple Copy and Paste Macro (looking for Values ONLY to be INSERTED)

Posted on 2014-10-15
16
335 Views
Last Modified: 2014-10-16
Hi Experts,
I'm having a bit of trouble with a simple copy and paste macro, please see the code below:
Public Sub SMAddtoReport()
Dim StartRow As Integer
AppendCounter = Sheets("Par-Indirects").Range("R10")

Sheets("GotoRepHelper").Activate
Range("A15:I68").Select
Selection.Copy
Sheets("GotoReport").Select
StartRow = ((AppendCounter * 15) + 15)
Range("A" & StartRow).Select
Selection.Insert Shift:=xlDown
Selection.PasteSpecial Paste:=xlPasteValues
End Sub

Open in new window


Essentially, "GotoRepHelper" is a worksheet with a bunch of Indirects and cell references that I would like the values from pasted to "GotoReport". I'd like the range from GotoRepHelper to be inserted into GotoReport shifting cells down and then for only formatting and values to be present what am I doing wrong.... I'm getting a:

Run time error 1004
Saying the information cannot be pasted because the Copy Area and the paste area are not the same size and shape.
0
Comment
Question by:-Polak
  • 7
  • 7
  • 2
16 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40383222
Did you try recording a macro?
0
 
LVL 1

Author Comment

by:-Polak
ID: 40383378
Yes, but to my knowledge there isn't a function on the UI that let's you insert values only. You can paste values only, but not insert them.....
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40383402
I'm not sure what you mean. This code inserts a cell and then writes to it.

    Range("A10").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.Value = "aaaa"
0
 
LVL 1

Author Comment

by:-Polak
ID: 40383476
I don't think that will help, I've gone ahead and mocked up a version of my workbook to aid in understanding my need.

Just trying to get the specified range (formatting and values only) to insert from GotoReportHelper to GotoReport. I can get it to paste formats and then values but not insert....

PS AppendCounter takes care of where the range is to be inserted.
EESafe-AddtoReport-10.15.xlsm
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40384314
When you have values copied to the clipboard (marching ants around copied area), press Ctrl & + and you will have an option to Move Right or Move Down to accommodate new values.

Thanks
Rob H
0
 
LVL 1

Author Comment

by:-Polak
ID: 40384357
Hummm, I think you mean CTRL & Shift & + but I'm not sure how that helps me.... the code associated with CTRL & Shift & + is still Selection.Insert Shift:=xlDown.... the difficulty I'm having is once it is inserted I want the values (and formatting) to come over to GotoReport not the formulas from GotoReportHelper.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40384397
See if this gave the results you wanted. Note that I made a copy of the GoToReport sheet as a backup.
Q-28538407.xlsm
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40384577
You could do it in 3 steps:

1) Ctrl & + to insert and paste
2) Paste special values
3) Paste special formats

You may be able to combine 2 & 3 as there is a paste special option for values and number formats.

Thanks
Rob
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 1

Author Comment

by:-Polak
ID: 40384584
See if this gave the results you wanted. Note that I made a copy of the GoToReport sheet as a backup.
Pretty much, with the exception of cell formatting?
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40384854
Added rows 18 to 21.
Public Sub SMAddtoReport()
Dim StartRow As Integer
Dim rngDest As Range
Dim rngFrom As Range
Dim rngTo As Range

AppendCounter = Sheets("Par-Indirects").Range("R10")

StartRow = ((AppendCounter * 15) + 15)

Set rngFrom = Sheets("GotoRepHelper").Range("A15:I68")
Set rngTo = Sheets("GotoReport").Range("A" & StartRow)
Set rngDest = rngTo.Resize(rngFrom.Rows.Count, rngFrom.Columns.Count)
Application.CutCopyMode = False
rngDest.Insert shift:=xlDown
rngDest.Offset(-rngFrom.Rows.Count).Value = rngFrom.Value

rngFrom.Copy
Set rngTo = Sheets("GotoReport").Range("A" & StartRow)
Set rngDest = rngTo.Resize(rngFrom.Rows.Count, rngFrom.Columns.Count)
rngDest.PasteSpecial Paste:=xlPasteFormats

End Sub

Open in new window

0
 
LVL 1

Author Comment

by:-Polak
ID: 40384877
Nearly there! I was going to use AppendCounter = AppendCounter + 1 as my method of placing the next instance of Range("A15:I68") below the previous paste, but your solution seems cleaner... how do I add a row space to rngDest though?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40384930
Are you saying that you want a new, blank, row after row 126?
0
 
LVL 1

Author Comment

by:-Polak
ID: 40384950
Nevermind, I just changed the selection range so that it included one row down of blank cells. Thank you so much for your help Martin, final workbook is attached.
Q-28538407-Polak.xlsm
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40384993
Sorry to have to ask, but are you going to assign points?
0
 
LVL 1

Author Closing Comment

by:-Polak
ID: 40385143
Of Course :-p
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40385169
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now