Copy And Past On To Next Empty Row

[bloodmilksky]

Hi Guys, I was wondering if anyone can help.

I am trying to use the below to compile an order of different products. ideally what should happen is that it copies Range B769 to sheet 1 and then you can add another group of products to the next empty row. but what is happening at the moment is that everytime you run it, it is just copying over the previous order. can anyone help please ?

Dim lRow As Long
Dim sRangeName As String


    'Copy Cells B7 & D68 On Menu
    Sheets("Menu").Range("B7:D68").Copy
    'To A Range Defined in B5
    sRangeName = Sheets("MENU").Range("B5").Value


    'The range is on menu sheet and is the range defined on sheet1 "a1:d880"
    'get next empty row in named range
    lRow = 1
    Do Until Sheets("Sheet1").Range(sRangeName).Cells(lRow, 1) = ""
        lRow = lRow + 1
    Loop
    'Paste Data
    Sheets("Sheet1").Range(sRangeName).Cells(lRow, 1).PasteSpecial xlPasteAll

   End Sub

http://www.mrexcel.com/forum/excel-questions/974247-copy-past-large-amounts-data.html

[onlyadrafter]

Hello,

think I may have missed something but is it not just

Sub TEST()
    Sheets("Menu").Range("B7:D68").Copy
    Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
End Sub

[bloodmilksky]

Thank you ^_^

is it possible to amend this code so that it will only copy the range if it meets a certain critera? so if only copy the rows where the value in column D exceeds a value of 0

Many thanks

Jamie

[onlyadrafter]

Hello,

Does this work as expected?

Sub TEST()    Application.ScreenUpdating = False
    With Sheets("Menu")
        With .Range("B7:D68")
            .AutoFilter
            .AutoFilter Field:=3, Criteria1:=">0", Operator:=xlAnd
        End With
        .Range("B7:D68").SpecialCells(xlCellTypeVisible).Copy
    End With
    Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
    Range("B7:D68").AutoFilter
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

[bloodmilksky]

Hi sorry for the delay in coming back to you I have been ill and thank you for taking the time to reply to me.

Whats happening when I run the macro is that it is only copying the formulas and not the values so when its pasting the information is incorrect. I have tried amending it but it still does the same thing

[mana]

Sub test()

    With Sheets("Menu").Range("B7:D68")
        .AutoFilter
        .AutoFilter Field:=3, Criteria1:=">0"
        If .Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
            Intersect(.Cells, .Offset(1)).Copy
            Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
            Application.CutCopyMode = False
        End If
        .AutoFilter
    End With

End Sub

[Aussiebear]

What's happening when I run the macro is that it is only copying the formulas and not the values so when its pasting the information is incorrect.

The developers of Excel have allowed for this type of procedure to take place by using the Paste special, Values Only option, which you can experiment with yourself. Mana's code will cover the VBA side of the same process.