+ Reply to Thread
Results 1 to 6 of 6

VBA Paste Transpose Into New Worksheet First Open Row

  1. 04-02-2019, 04:09 PM #1
    bigtunelover
    bigtunelover is offline
    Registered User
    Join Date
    01-27-2012
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2016, O365
    Posts
    97

    VBA Paste Transpose Into New Worksheet First Open Row

    Hi Everyone,

    I am working on a project where I need to copy a range of cells (C3:C30) in a column and then paste "values only" and transpose into the first blank cell in column A on another worksheet.

    I have tried recording a macro but when it runs, it always pastes into a static cell. (the one i clicked on when recording which was blank)

    I am trying to then clear the original range of the selected drop downs text (but not the data validation for each cell) with the intent of clicking a button to paste the data into the next worksheet indefinitely and resetting the original form for use.

    Thoughts? Suggestions?

    I have uploaded a mock-up of the workbook I am referring to.

    Thanks for your help!
    Les
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 04-02-2019, 05:39 PM #2
    JLGWhiz
    JLGWhiz is offline
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: VBA Paste Transpose Into New Worksheet First Open Row

    This should copy the range and paste it to the next available row in the 'Paste Samples' sheet.

    Please Login or Register  to view this content.
    The following bit of code finds the next available row in column A of the destination sheet by going to the bottom of the column and then looking upward until if finds data, then offsets to the next row down.
    Cells(Rows.Count, 1).End(xlUp)(2)
    If column A is not as long or longer than all other columns then any data in that row for the other columns would be overwritten by the data being pasted. In your case, that should not be a problem, but you need to bear that in mind if your use that method elsewhere.
    Last edited by JLGWhiz; 04-02-2019 at 05:45 PM.
    Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
    Just when I think I am smart, I learn something new!
    Reply With QuoteRegister To Reply
  3. 04-03-2019, 09:49 AM #3
    bigtunelover
    bigtunelover is offline
    Registered User
    Join Date
    01-27-2012
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2016, O365
    Posts
    97

    Re: VBA Paste Transpose Into New Worksheet First Open Row

    Thanks so much for your reply!

    Did you end up getting this to successfully work in the Mockup?

    I am getting "Compile error: Syntax error

    Les
    Last edited by bigtunelover; 04-03-2019 at 12:40 PM.
    Reply With QuoteRegister To Reply
  4. 04-03-2019, 10:05 AM #4
    JLGWhiz
    JLGWhiz is offline
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: VBA Paste Transpose Into New Worksheet First Open Row

    There was a comma missing after xlPasteValues
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 04-03-2019, 04:21 PM #5
    bigtunelover
    bigtunelover is offline
    Registered User
    Join Date
    01-27-2012
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2016, O365
    Posts
    97

    Re: VBA Paste Transpose Into New Worksheet First Open Row

    Awesome! Thanks so much. That helped a lot!
    Les
    Reply With QuoteRegister To Reply
  6. 04-03-2019, 06:40 PM #6
    JLGWhiz
    JLGWhiz is offline
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: VBA Paste Transpose Into New Worksheet First Open Row

    You're welcome, thanks for the reps
    regards, JLG
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Message box to open when copy/paste procedure is used in the worksheet and run a macro
    By PaulM100 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-05-2018, 11:12 AM
  2. [SOLVED] Excel 2007: Copy Column to another worksheet, transpose paste
    By wendie1970 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-04-2012, 03:54 PM
  3. VBA to Open document, then paste 1 column from worksheet A to Worksheet B.
    By ghladik in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-24-2012, 11:27 AM
  4. [SOLVED] what the opposite of PASTE TRANSPOSE? I want to paste down from paste transpose...
    By Exxcel Noob in forum Excel General
    Replies: 4
    Last Post: 05-11-2012, 02:24 PM
  5. Open file into excel, copy certain cells, and paste them into a new worksheet.
    By dg980 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-12-2011, 10:41 AM
  6. Copy and paste transpose on next open row.
    By jshot99 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-14-2011, 03:10 PM
  7. TRANSPOSE ROWS INTO COLUMS without useing PASTE SPECIAL; TRANSPOSE
    By annepopa in forum Excel General
    Replies: 2
    Last Post: 02-19-2007, 04:53 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1