Copy all data to another sheet without select any range and clear the original contents

[Kenor]

Hi guys,
Sorry actually I'm not so familiar with VBA code.
I want to transfer data from worksheet 'Register' to worksheet 'Database' in same workbook.
I would like to have Transfer button. So, when I click the Transfer button all data from worksheet 'Register' will paste on next blank row in worksheet 'Database' and clear the original contents.
I have some code below. But let say I want all data transfer automatically in worksheet 'Database' without mention specific Range.
Means, I want to transfer all available data. For example, today will transfer data from A2:E5 but tomorrow maybe need to transfer data A2:E10. Everyday data might not in specific range.

Anybody can help me. I'm not sure how to modify below code as per I mention above.


Sub CopyPasteBelowLastCell()
'
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

Set wsCopy = Worksheets("Register")
Set wsDest = Worksheets("Database")

Range("A2:D9").Select
Selection.Copy
Sheets("Database").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("Register").Select
Range("A2:D9").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("D17").Select

End Sub

 

[Zot]

Without modification also it will work as is, right?

Can be simplified to

Sub CopyPasteBelowLastCell()
'
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

Set wsCopy = Worksheets("Register")
Set wsDest = Worksheets("Database")

wsCopy.Range("A2:D9").Copy Destination:=wsDest.Range("A2")
Application.CutCopyMode = False
wsCopy.Range("A2:D9").ClearContents

End Sub

 

[Kenor]

Hi Zot,

Yes it work.

But how if I want to transfer data without edit/mention the range every time I click the transfer button.
I want it automatically can read the data available in worksheet "Register" and transfer to next blank row in worksheet "Database".

Is it can do that?

I'm not sure what is the best code can replace for Range("A2:D9") to make it can read any available data range.

Btw, thanks because u make it simple code..

 

[Zot]

Sub CopyPasteBelowLastCell()

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

Set wsCopy = Worksheets("Register")
Set wsDest = Worksheets("Database")

wsCopy.Cells.Copy Destination:=wsDest.Range("A1")
Application.CutCopyMode = False
wsCopy.Cells.ClearContents

End Sub

This will copy the whole sheet and paste everything onto another sheet.

 

[Kenor]

How if I want to transfer data only? Not include the header.

Means, every time I transfer new register data, only data will go to next blank row in worksheet Database.

Both worksheet already have header with same title.

 

[Zot]

Meaning the headers are not at the same location respective to wsCopy. Data is copied to empty rows according to Header. The databse will accumulate over time with new data from wsCopy (Register).

If that is so, then, need to specify Headers' locations in wsCopy and wsDest so that it will go to right column. Headers can also be searched in specific range but if specified, probably much faster to copy.

 

[Kenor]

I make it simple code as below,

Sub Copy_Paste_First_Blank_Cell()

Range("A2", Range("D" & Rows.Count).End(xlUp)).Cut Worksheets("Summary").Range("A65536").End(xlUp)(2)
Application.CutCopyMode = False

End Sub

But, after all data transfer and clear, I click again on the Transfer button. The Title (header) is missing in the original sheet. It has also been moved to another sheet.

How to maintain it?

What should I added on above code?

 

[Zot]

I cannot understand what you really want to do. Can you show how the Register and Database worksheets look like?

 

[Kenor]

So sorry if my description is a bit confusing.

This is example of transfer data from sheet "Export" to sheet "Summary".

 

[Kenor]

Sorry, below is the actual vba code I used.

Sub Copy_Paste_First_Blank_Cell()

Range("A2", Range("D" & Rows.Count).End(xlUp)).Cut Worksheets("Summary").Range("A65536").End(xlUp)(2)
Application.CutCopyMode = False


End Sub