Results 1 to 3 of 3

Thread: Copy Cells To empty Positions On Different Sheet

Hybrid View

  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
    3,574

    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
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    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
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

Posting Permissions

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



Click Here to Expand Forum to Full Width