Having difficulty copying a range of cells to another WS in same book at one row underneath last used row

[Desmondo]

I have been at this now for days now trying everything i can find and adapting it.

I want to copy data from one sheet to another at exactly one row beneath the last used row in the store sheet. I have had mixed results but none that i can find does exactly what i need and looking for some helpful pointers.

ws1 one is temp storage and ws2 is the store, when i click submit i want the used range from ws1 to be added to the row under the last used row of sheet2. This will be a continuous thing with more entries.

This is what i have so far and really hope someone here can help?

Sub moveDataToStore()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim LastRow
Dim myRange As Range




Set ws1 = Sheets("Temp")
Set ws2 = Sheets("Store")




myRange = ws1.UsedRange.CurrentRegion


With myRange


If Sheets("Store").Range("A1").Value = "" Then


.Copy Destination:=ws2.Cells(1, 1)


Else


LastRow = ws2.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row


.Copy Destination:=ws2.Cells(LastRow + 1).Row


End If




End With






ws1.Range("A:G") = ""

 

[Desmondo]

Exact sheet names are "Temp" and "Store", i want to copy from "Temp" to "Store" without overwriting whats in "Store" thanks

 

[My Aswer Is This]

Now like I said before and you never responded as to if this was true.
This script assumes there is always data in column "A" of each row. If that is not true then this may not work.
And then I would need to know what column always has date on each row.

Sub Copy_Sheet1_To_Sheet2()
'Modified 1-30-18 7:40 PM EST
Application.ScreenUpdating = False
Dim One As Long
Sheets("Temp").Activate
One = 1
Dim Lastrow As Long
Dim Lastrowa As Long
Lastrow = Sheets("Temp").Cells(Rows.Count, "A").End(xlUp).Row
Lastrowa = Sheets("Store").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Temp").Rows(One & ":" & Lastrow).Copy Sheets("Store").Rows(Lastrowa)
Application.ScreenUpdating = True
End Sub

 

[jolivanes]

You could put this as part of the code before you start the copy/paste bit.

Dim lr As Long, ws2 As Worksheet
Set ws2 = Worksheets("Store")
If WorksheetFunction.CountA(ws2.Cells) = 0 Then
lr = 1
Else
lr = ws2.Cells.Find("*", , , , xlByRows, xlPrevious).Offset(1).Row
End If

 

[jolivanes]

What is the result of this?

Dim lr As Long, ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Worksheets("Temp")
Set ws2 = Worksheets("Store")
If WorksheetFunction.CountA(ws2.Cells) = 0 Then
lr = 1
Else
lr = ws2.Cells.Find("*", , , , xlByRows, xlPrevious).Offset(1).Row
End If
ws1.UsedRange.Copy ws2.Cells(lr, 1)

 

[Desmondo]

What is the result of this?

Dim lr As Long, ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Worksheets("Temp")
Set ws2 = Worksheets("Store")
If WorksheetFunction.CountA(ws2.Cells) = 0 Then
lr = 1
Else
lr = ws2.Cells.Find("*", , , , xlByRows, xlPrevious).Offset(1).Row
End If
ws1.UsedRange.Copy ws2.Cells(lr, 1)

Hi sorry I fell asleep as 1 in the morning. I will try today and the column with data in it is column b. Thanks again

 

[Desmondo]

Hi sorry I fell asleep as 1 in the morning. I will try today and the column with data in it is column b. Thanks again

Your the man, works perfectly. Cheers