PDA

View Full Version : Copy And Past On To Next Empty Row



bloodmilksky
11-07-2016, 08:38 AM
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 B7:D69 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
11-07-2016, 02:28 PM
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
11-09-2016, 03:14 AM
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
11-09-2016, 12:16 PM
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
11-16-2016, 04:15 AM
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
11-16-2016, 07:04 AM
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
11-16-2016, 03:13 PM
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.