Macro to move a row changing pivot table data source

sarathomson13

New Member
Joined
Aug 11, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Watch MrExcel Video

Forum statistics

Threads
1,127,136
Messages
5,622,936
Members
415,941
Latest member
georgiana686

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