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") = ""

 

[jolivanes]

Like this maybe?

ws1.UsedRange.Copy ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1)

or if your columns in ws2 are different length, use the lr as you have in your code.

 

[Desmondo]

Thanks for the reply but still having same error pointing to - myRange = ws1.UsedRange.CurrentRegion, runtime error 91 Object variable or with block variable not set

 

[jolivanes]

I don't see

myRange = ws1.UsedRange.CurrentRegion

in my post at all

Maybe explain what you want to accomplish.
Showing code that does not work is not something I like to work with. Everyone does things different.

 

[Desmondo]

Hi thanks again, It is partially working now. What i have is some information which is completed through a userform which updates a sheet called temp. I wish to move that data when i click the submit button to the sheet called store. The temp file is almost like a table with three columns A:C and varying length columns. I want to be able to add the next lot of data directly underneath the newly stored data in the same columns A:C in the store sheet where it sorted (Blank rows removed and some formatting applied like borders, shading etc) without erasing any off the first submission until i am finished with the case, when i will reset the columns in time for my next case. I hope this is clearer

It hits the temp sheet exactly as i want it and the formatting is bang on, i just can't get it to load directly into the next empty row in the store sheet. Think i need to find last row of store sheet and copy to next row so last row + 1.

 

[My Aswer Is This]

If your wanting to copy all the data from Sheet(1) to Sheet(2) not including Row(1)
Why not use this.

This is assuming both sheets have data in column (A)

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

 

[Desmondo]

Hi, i really appreciate your time with this. I do want to copy all the data including headers directly to the next empty row in sheet 2 without overwriting the previous data. And when i am ready to submit more data it then finds the next empty row again and copy's it in on the submit button..Until i am finished then i clear.

It works so far and submits correctly the first lot of data like this

but the else statement i have is not working.

Dim ws1 As WorksheetDim ws2 As Worksheet
Dim LastRow As Long






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




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




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


ws1.UsedRange.Copy ws2.Cells(1, 1)




Else


ws1.UsedRange.Copy ws2.Cells(LastRow + 1)




End If




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


End Sub

 

[My Aswer Is This]

So my script should do what you want:
Just change this:

One = 2

To:

One =1
​

_{^{<strike>
</strike>}}​

 

[Desmondo]

Not quite working, it stores the data in the format that is needed, in the temp worksheet but instead of moving over on submit to the store sheet it overwrites the temp file with the newly submitted data.

 

[My Aswer Is This]

Look I'm using the term Sheet(1) and Sheet(2)

You need to Give me the exact name of the two sheet.

I want to see something like this:

Copy From Sheet name is "Bill"
Copy To sheet name is "Jane"

Put the exact sheet name in quotes like I did.