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

Kenor

New Member
Joined
Dec 8, 2020
Messages
46
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
932
Office Version
  1. 2016
Platform
  1. Windows
Without modification also it will work as is, right?

Can be simplified to

VBA Code:
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

New Member
Joined
Dec 8, 2020
Messages
46
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Nov 26, 2020
Messages
932
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
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

New Member
Joined
Dec 8, 2020
Messages
46
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Nov 26, 2020
Messages
932
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Dec 8, 2020
Messages
46
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Nov 26, 2020
Messages
932
Office Version
  1. 2016
Platform
  1. Windows
I cannot understand what you really want to do. Can you show how the Register and Database worksheets look like?
 

Kenor

New Member
Joined
Dec 8, 2020
Messages
46
Office Version
  1. 2016
Platform
  1. Windows
So sorry if my description is a bit confusing.

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

Attachments

  • Export data.PNG
    Export data.PNG
    32.4 KB · Views: 9
  • Summary Sheet.PNG
    Summary Sheet.PNG
    12.2 KB · Views: 9
  • VBA editor.PNG
    VBA editor.PNG
    30.7 KB · Views: 9

Kenor

New Member
Joined
Dec 8, 2020
Messages
46
Office Version
  1. 2016
Platform
  1. Windows
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
 

Attachments

  • VBA editor.PNG
    VBA editor.PNG
    24.2 KB · Views: 4

Watch MrExcel Video

Forum statistics

Threads
1,127,009
Messages
5,622,145
Members
415,881
Latest member
tasic89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top