Solved

Excel: Paste value to next blank row after X

Posted on 2015-02-03
5
166 Views
Last Modified: 2016-02-11
Hello,

Seeking your help on the following -

I would like to copy a cell and paste it to another sheet - to the first blank row in column A (after row A8).

I currently have this code - can you please assist:

Sub mac_pastevalue
'
' mac_pastevalue


' Here I copy a cell and then choose another active sheet

    Sheets("PO Template").Select
    Range("R12").Select
    Selection.Copy
    Sheets("PO Request Summary").Select

'Here I want to paste the above into the first blank row in column A, after A8
  

End Sub

Open in new window

0
Comment
Question by:dabug80
[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
  • 3
  • 2
5 Comments
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40587643
You can try this:

  Sheets("PO Template").Select
        Range("R12").Select
        Range(Selection, Selection.End(xlDown)).Copy Destination:=Sheets("PO Request Summary").Range("A8" & Rows.Count).End(xlUp).Offset(1)

Open in new window


or


  Sheets("PO Template").Range("R12").Select
        Range(Selection, Selection.End(xlDown)).Copy Destination:=Sheets("PO Request Summary").Range("A8" & Rows.Count).End(xlUp).Offset(1)

Open in new window

0
 
LVL 1

Author Comment

by:dabug80
ID: 40587664
Thanks. I tried both. And I get the same error.

For the two line code I get:

Runtime error: 1004:

Application-defined or object-defined error

With the debug on line 2.
0
 
LVL 11

Accepted Solution

by:
Wilder1626 earned 400 total points
ID: 40587678
sorry. Try link this

Sheets("PO Template").Range("R12").Copy Destination:=Sheets("PO Request Summary").Range("A8").End(xlUp).Offset(1, 0)

Open in new window

0
 
LVL 1

Assisted Solution

by:dabug80
dabug80 earned 0 total points
ID: 40587701
Thanks,

I altered the code to read 'Down' instead of 'Up' and it worked as needed. Here's the final code:

Sub mac_testpaste()
'
' mac_testpaste Macro

Sheets("PO Template").Select
        Range("R12").Select
        Sheets("PO Template").Range("R12").Copy Destination:=Sheets("PO Request Summary").Range("A8").End(xlDown).Offset(1, 0)
End Sub

Open in new window

0
 
LVL 1

Author Closing Comment

by:dabug80
ID: 40596625
Small adjustment to the code to make the VBA add values from the bottom, not the top.
0

Featured Post

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!

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

691 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