Copy Cells To empty Positions On Different Sheet-VBForums
Results 1 to 3 of 3

Thread: Copy Cells To empty Positions On Different Sheet

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2012
    Posts
    9

    Copy Cells To empty Positions On Different Sheet

    Hi Was wondering if sombody could help

    I have a excel problem

    i need to copy a complete row of data into a new sheet but the code needs to move to the next blank row

    I was thinking of using the Selection.End(xlDown).Select command but i have to admin my VB isnt up to scratch to use it

    If i had the row of data in Sheet 1 row 2 and i need the whole row pasted in the next available row in sheet 2

    is there a simple code to do it

    thanks

    steve

  2. #2
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    2,449

    Re: Copy Cells To empty Positions On Different Sheet

    There are better ways, but start with something like this:

    Code:
    Sub copy_row()
        ActiveCell.Rows("1:1").EntireRow.Select   'selects entire row of active cell
        Selection.Copy
        Sheets("Sheet2").Select
        Range("a65536").Select
        Selection.End(xlUp).Select
        ActiveCell.Offset(1, 0).Range("A1").Select
        ActiveSheet.Paste
    End Sub

  3. #3
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    Re: Copy Cells To empty Positions On Different Sheet

    @vbfbryce: I wouldn't recommend that method

    Reasons

    1) Avoid words like ActiveCell, Selection. Directly perform the action that you want to do.
    2) Avoid words like .Select. They are a major cause of errors and also slow down your code.
    3) Avoid Hard-coded values like "65536". What if the user is using Excel 2007/2010/2011 and has data up till 70k rows?

    @steveb1471: Welcome to the forums

    I wouldn't recommend xlDown as it will give you undesired results if your data in Col A will have blank cells in between. See this code. I have commented it so you shouldn't have any problem understanding it. Still if you do then do not hesitate to ask

    Code:
    Sub Sample()
        Dim wsI As Worksheet, wsO As Worksheet
        Dim LastRow As Long
        
        Set wsI = Sheets("Sheet1") '<~~ Input Sheet
        Set wsO = Sheets("Sheet2") '<~~ Output Sheet
        
        '~~> Get the next available row in Sheet2
        LastRow = wsO.Range("A" & Rows.Count).End(xlUp).Row + 1
        
        '~~> Copy row 2 from sheet1 to next available row in Sheet2
        wsI.Rows(2).Copy wsO.Rows(LastRow)
    End Sub
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.