Results 1 to 7 of 7

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

Threaded View

  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

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