?
Solved

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

Posted on 2014-10-15
16
Medium Priority
?
609 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
  • 2
16 Comments
 
LVL 49

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 49

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 33

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 49

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 33

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
 
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 49

Accepted Solution

by:
Martin Liss earned 2000 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 49

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 49

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 49

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

762 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