Macro to move a row changing pivot table data source

[sarathomson13]

Hi, Apologies if there are forms about this already I wasn't sure of the correct terminology.

I have made up a new database for work to monitor our caseload. It is working well and I decided to set up some pivot tables to monitor team statistics for our manager.

One of the Macros I have is to copy a row from the main caseload worksheet and paste it into the Discharge Worksheets. Then deleting the original row from the caseload worksheet.

When I use this macro it then alters the source data for my pivot tables changing it from the selected area of the worksheet "MDT Caseload'!$A$2:$AA$1000" to a single row. I think when I am using the transfer to discharge macro it is changing the active area.

I get an error message coming up when I try to refresh stating: This command requires at least two rows of source data. You cannot use the command on a selection in only one row.

Is there some code I can add to the bottom to prevent this happening and keep the full table as the Active area? Row A is not included in the main tables but the table headings are in row B between colum A and AA. My code writing skills and pretty basic and this took me a few nights of youtube watching to try and figure out.

Sub TransferDC()
'
' TransferDC Macro
'

'
Rows(ActiveCell.Row).Select
Selection.Cut
Sheets("Discharges").Select
Range("A1:AA1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Range("A1:AA1").Select
Sheets("ReACH FV MDT Caseload").Select
Selection.Delete Shift:=xlUp
Range("A1:AA1").Select

Any help or advice would be much appreciated.