VBA to copy data from one worksheet to another and keep it when the source is deleted daily

bazbuyer

New Member
Joined
Dec 20, 2017
Messages
27
Office Version
  1. 2016
I've posted a problem re Get&Transform append. Perhaps there is a VBA solution to what I want to do. I have a source data sheet S and destination sheet D in another workbook. I want to delete the data completely in S on a daily basis and put new data in S (usually on a row or 2 per day) and then update the new data to D without deleting the previous. So S is a totally new database each day, D grows day by day and maintains all the previous data. So it cuts the rows from one range (s) which vary in number and pastes them on the next available row in D thanks
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Workbook 1 will be the one for source and workbook 2 will be the one for destination. You will have to modify the code with the correcte names for the workbooks and the sheets.
Code:
Sub t()
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet
Set wb1 = Workbooks(1) 'Substitute actual name since indexs are unreliable
Set wb2 = Workbooks(2) 'Same here
Set sh1 = wb1.Sheets("S") 'Edit sheet name
Set sh2 = wb2.Sheets("D") 'Edit sheet name
sh1.UsedRange.Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2) 'Copies data from source to destination, next avail row.
wb2.Save
sh1.UsedRange.ClearContents 'Clears data from source sheet, ready for next input.
End Sub
When the macro runs, it takes any data on the sheet and pastes it to the destination sheet. It then clears the source sheet so that new data can be added.
 
Upvote 0
Workbook 1 will be the one for source and workbook 2 will be the one for destination. You will have to modify the code with the correcte names for the workbooks and the sheets.
Code:
Sub t()
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet
Set wb1 = Workbooks(1) 'Substitute actual name since indexs are unreliable
Set wb2 = Workbooks(2) 'Same here
Set sh1 = wb1.Sheets("S") 'Edit sheet name
Set sh2 = wb2.Sheets("D") 'Edit sheet name
sh1.UsedRange.Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2) 'Copies data from source to destination, next avail row.
wb2.Save
sh1.UsedRange.ClearContents 'Clears data from source sheet, ready for next input.
End Sub
When the macro runs, it takes any data on the sheet and pastes it to the destination sheet. It then clears the source sheet so that new data can be added.
Great thanks this looks like a nice simple solution
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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
Back
Top