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
23
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
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.
 

bazbuyer

New Member
Joined
Dec 20, 2017
Messages
23
Office Version
  1. 2016
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
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
You're welcome,
Regards, JLG
 

Watch MrExcel Video

Forum statistics

Threads
1,122,976
Messages
5,599,140
Members
414,292
Latest member
kingshuk963

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