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

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.
LVL 1
-PolakAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Did you try recording a macro?
0
-PolakAuthor Commented:
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
Martin LissOlder than dirtCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

-PolakAuthor Commented:
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
Rob HensonFinance AnalystCommented:
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
-PolakAuthor Commented:
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
Martin LissOlder than dirtCommented:
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
Rob HensonFinance AnalystCommented:
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
-PolakAuthor Commented:
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
Martin LissOlder than dirtCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
-PolakAuthor Commented:
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
Martin LissOlder than dirtCommented:
Are you saying that you want a new, blank, row after row 126?
0
-PolakAuthor Commented:
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
Martin LissOlder than dirtCommented:
Sorry to have to ask, but are you going to assign points?
0
-PolakAuthor Commented:
Of Course :-p
0
Martin LissOlder than dirtCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.