Solved

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

Posted on 2014-10-15
16
431 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 46

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 46

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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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 46

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 46

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 46

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 46

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 46

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

856 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