Copy Variable Range To Next Blank Row

Re: Macro To Copy To Next Blank Row

Try this:

Sub Move_New_Account()
Dim Rng As Range, MyCell As Range, Rng1 As Range
Set Rng = Sheets("Account Input").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
Set Rng1 = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Rng.EntireRow.Copy Destination:=Rng1
End Sub

Re: Macro To Copy To Next Blank Row

This should get you started:

Option Explicit
Sub CopyNewAccnt()


    Dim rngCopy As Range, rngPaste As Range


    Application.ScreenUpdating = False


    Set rngCopy = Sheet2.Range("A3:C3")
    Set rngPaste = Sheet1.Range("A1").End(xlDown).Offset(1, 0)


    On Error GoTo 0
    If Sheet2.Range("A3") <> vbNullString Then


        rngCopy.Copy Destination:=rngPaste


    Else
        Exit Sub


    End If


    Set rngCopy = Nothing
    Set rngPaste = Nothing
    Application.ScreenUpdating = True


End Sub

Re: Copy Variable Range To Next Blank Row

You wouldn't want to cut and paste just add Rng.Clearcontents to the end of my code before end sub. as for the others highlight them in the VBE then hit F1

Re: Copy Variable Range To Next Blank Row

Quote

if I want it to cut and paste as opposed to copy what do I need to change?

Amended code:

Option Explicit 
Sub CopyNewAccnt() 
     
    Dim rngCopy As Range, rngPaste As Range 
     
    Application.ScreenUpdating = False 
     
    Set rngCopy = Sheet2.Range("A3:C3") 
    Set rngPaste = Sheet1.Range("A1").End(xlDown).Offset(1, 0) 
     
    On Error Goto 0 
    If Sheet2.Range("A3") <> vbNullString Then 
         
        rngCopy.Copy Destination:=rngPaste 
        rngCopy.ClearContents 'clear contents of the source range


    Else 
        Exit Sub 
         
    End If 
     
    Set rngCopy = Nothing 
    Set rngPaste = Nothing 
    Application.ScreenUpdating = True 
     
End Sub

Quote

1. what does vbnullstring mean/do?

Basically, just checks to make sure the cell is not empty. As "Simon says" (OK, bad pun, but I couldn't help it. :grin:), press F1 in the VB Editor to read the help on this.

Quote

2. what does x1Down do?

Selects the cell at the bottom of column A in the region that contains cell A1.

Re: Copy Variable Range To Next Blank Row

I tried it and it awesome.
however, if i use it on excel with group outline (collapse mode) the script is not work.
Attached with sample file for ref. You will get what i mean before and after you add new table between expand mode and collapse mode.
Appreciate your help to solve this problem.

Thank you.