+ Reply to Thread
Results 1 to 7 of 7

VBA to copy various cells in sheet 2 and find next blank WHOLE row and paste

  1. #1
    Registered User
    Join Date
    04-09-2013
    Location
    Mt barker,Western Australia
    MS-Off Ver
    Excel 2010
    Posts
    16

    VBA to copy various cells in sheet 2 and find next blank WHOLE row and paste

    Hello,
    I have a workbook with the following VBA. It mainly works but when it pastes to the relevant column it doesn't pick last whole row just the last blank cell in that column.
    sheet two is a basic sheet with data on it. I want it to enter particular cells in next blank row but all data in same row. Need Help

    Sub CopyPaste()
    Sheets(2).Range("B1").Copy
    Sheets(1).Cells(Cells.Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    Sheets(2).Range("J1").Copy
    Sheets(1).Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    Sheets(2).Range("A5").Copy
    Sheets(1).Cells(Cells.Rows.Count, 3).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    Sheets(2).Range("A6").Copy
    Sheets(1).Cells(Cells.Rows.Count, 4).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    Sheets(2).Range("A7").Copy
    Sheets(1).Cells(Cells.Rows.Count, 5).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    Sheets(2).Range("A8").Copy
    Sheets(1).Cells(Cells.Rows.Count, 6).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    Sheets(2).Range("A9").Copy
    Sheets(1).Cells(Cells.Rows.Count, 7).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    Sheets(2).Range("A10").Copy
    Sheets(1).Cells(Cells.Rows.Count, 8).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    Sheets(2).Range("B11").Copy
    Sheets(1).Cells(Cells.Rows.Count, 9).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    ActiveSheet.PrintOut
    Sheets(2).Range("B1").ClearContents
    Sheets(2).Range("A5:A10").ClearContents
    Sheets(2).Range("B11").ClearContents
    Sheets("Register").Select
    ThisWorkbook.Save
    End Sub
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    08-28-2015
    Location
    Melbourne, AUS
    MS-Off Ver
    2011
    Posts
    59

    Re: VBA to copy various cells in sheet 2 and find next blank WHOLE row and paste

    If one column is always complete I use that as the reference, in this case I will reference column A (Rows.Count, 1).

    Sheets(1).Cells(Cells.Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues

    Would become

    Sheets(1).Range("B" & Cells(Rows.Count, 1).End(xlUp).Row).Select
    Selection.PasteSpecial Paste:=xlPasteValues


    Please Login or Register  to view this content.
    Last edited by mrshl9898; 08-07-2017 at 10:34 PM.

  3. #3
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,347

    Re: VBA to copy various cells in sheet 2 and find next blank WHOLE row and paste

    Or another way (also with some tidy up of the code)

    Please Login or Register  to view this content.
    If you want something done right... find a forum and ask an online expert.

    Time flies like an arrow. Fruit flies like a banana.

  4. #4
    Registered User
    Join Date
    04-09-2013
    Location
    Mt barker,Western Australia
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: VBA to copy various cells in sheet 2 and find next blank WHOLE row and paste

    The second set of formula seems to work but it always picks row 16 not the first available blank row?

  5. #5
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,347

    Re: VBA to copy various cells in sheet 2 and find next blank WHOLE row and paste

    Ahh yes - you start on the other sheet don't you and 16 is the next row there. OK try this.

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    04-09-2013
    Location
    Mt barker,Western Australia
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: VBA to copy various cells in sheet 2 and find next blank WHOLE row and paste

    SWEET works like a charm thanks so much, has saved me a big headache

  7. #7
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,347

    Re: VBA to copy various cells in sheet 2 and find next blank WHOLE row and paste

    OK, then please mark as solved (in the thread tools, top right) and add reputation as appropriate

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Find Non-Blank Cells and Paste to new sheet.
    By jwolfenstein in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-25-2014, 02:39 PM
  2. VBA: Copy non-blank and paste to separate sheet ONLY blank cells (running list)
    By brolsen in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-10-2014, 03:34 PM
  3. [SOLVED] Macro to find blank cells, copy row, paste into another worksheet, then delete
    By rbrookov in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 10-15-2013, 01:39 PM
  4. [SOLVED] Find value in a cell in different sheets and copy and paste other cells to another sheet
    By the-hawk in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-04-2013, 03:36 AM
  5. Copy and paste values into next sheet. (blank cells contain formulas)
    By Vlad717 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-14-2013, 06:54 AM
  6. Replies: 18
    Last Post: 02-10-2013, 05:32 PM
  7. [SOLVED] VB code help- need copy cells in a row and paste to new sheet on next available blank line
    By Barbara Excel in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 11-27-2012, 03:11 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1