Copy & Past Large Amounts of Data

[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 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 ?

Sub OrderCopy()


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

any help would be greatly appreciated

Jamie

 

[tonyyy]

Jamie,

You might consider the following...

Sub OrderCopy()

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

lRow = Sheets("Sheet1").Range(sRangeName).Rows.Count + 1
'Paste Data
Sheets("Sheet1").Range(sRangeName).Cells(lRow, 1).PasteSpecial xlPasteAll

End Sub

Cheers,

tonyyy

 

[bloodmilksky]

Hi,

It is just overwriting the previously Pasted data.

many thanks

jamie

 

[Kenneth Hobson]

The only reason I see to use a named range of A1:D880 for sheet1 would be to find the next empty row in that range.

Most people just use sheet1 column A's next empty cell. Does that work for you or MUST you have the next empty row in columns A:D?

Of course it may be that there are no empty rows when you limit it like that.

 

[bloodmilksky]

I havent tried that as of yet. how would i add that into the code?

 

[Kenneth Hobson]

Sub OrderCopy()
  'Copy/Paste Cells B7 & D68 On Menu to next empty row on sheet1
  Sheets("Menu").Range("B7:D68").Copy _
      Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub