Solved

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

Posted on 2014-10-15
16
472 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 47

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 47

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
Independent Software Vendors: 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 47

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 47

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 47

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 47

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 47

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

Industry Leaders: 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!

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

749 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