Best way to copy a section of a table?

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,687
Office Version
  1. 365
Platform
  1. Windows
This comes up fairly often, so I thought I'd see if there is a better way than the tedious way I am currently doing it.

The mini-sheet below is a table I use to track the NFL Power Rankings for the grandkids, who are big 49er fans. The new rankings come out every Tuesday. I need to make a copy of the 3 columns of the most recent section. The mini-sheet does not accurately display the table headings or the rows above the table. Those are formatted with the Center Across Selection alignment. This is how they really look.

1665512522232.png


2022 Power Rankings.xlsx
BCDEFGHIJKLMNOPQRSTU
4Week 5Week 4Week 3Week 2Week 1Week 0
510/11/2210/04/229/27/229/20/229/13/229/09/22
6TeamConfDivisionWk 5 RecordWk 5 RankWk 5 ΔWk 4 RecordWk 4 RankWk 4 ΔWk 3 RecordWk 3 RankWk 3 ΔWk 2 RecordWk 2 RankWk 2 ΔWk 1 RecordWk 1 RankWk 1 ΔWk 0 RecordWk 0 Rank
7Philadelphia EaglesNFCEast5-02-14-01=03-01+22-03+71-010+40-014
8Buffalo BillsAFCEast4-11+13-12=02-12-12-01=01-01=00-01
9Kansas City ChiefsAFCWest4-13=03-13+22-15-32-02=01-02+50-07
10Green Bay PackersNFCNorth3-28-43-14=02-14+11-15+30-18-20-06
11San Francisco 49ersNFCWest3-24+12-25+61-211-21-19+20-111-80-03
12Cincinnati BengalsAFCNorth2-39-32-26+41-210+20-212-30-19-40-05
13Baltimore RavensAFCNorth3-25+22-27=02-17+31-110-41-06+40-010
14Tampa Bay BuccaneersNFCSouth3-26+22-28=02-18-42-04-11-03+10-04
15Minnesota VikingsNFCNorth4-17+23-19=02-19+21-111-41-07+130-020
16Miami DolphinsAFCEast3-218-83-110-73-03+52-08+51-013+60-019
17Los Angeles RamsNFCWest2-315-42-211-52-16=01-16-20-14-20-02
18Tennessee TitansAFCSouth3-211+12-212=02-112+80-220-30-117=00-017
19Dallas CowboysNFCEast4-110+33-113=02-113+81-121+60-127-120-015
20Los Angeles ChargersAFCWest3-212+22-214+11-215-81-17-21-05+40-09
21Las Vegas RaidersAFCWest1-414+11-315+60-321-60-215-10-114-30-011
22Denver BroncosAFCWest2-323-72-216-22-114+31-117-10-116-80-08
23Arizona CardinalsNFCWest2-319-22-217+31-220-61-114+80-122-60-016
24New Orleans SaintsNFCSouth2-316+21-318+11-219-61-113-11-012+10-013
25New York GiantsNFCEast4-113+63-119+32-122-32-019+21-021+70-028
26Jacksonville JaguarsAFCSouth2-325-52-220-22-118+101-128+30-131-20-029
27Cleveland BrownsAFCNorth2-320+12-221-42-117+51-122-21-020+20-022
28Altanta FalconsNFCSouth2-322=02-222+31-225+40-229+10-130+10-031
29Seattle SeahawksNFCWest2-324-12-223+51-228-41-124-61-018+60-024
30Detroit LionsNFCNorth1-426-21-324-81-216=01-116+100-126-10-025
31New England PatriotsAFCEast2-321+41-325-11-224-61-118+50-123-50-018
32New York JetsAFCEast3-217+92-226+51-231-61-125+70-132-60-026
33Pittsburgh SteelersAFCNorth1-427=01-327-12-126-31-123-41-019+20-021
34Indianapolis ColtsAFCSouth2-2-128=01-2-128-51-1-123+30-1-126-110-0-115-30-012
35Washington CommandersNFCEast1-430-11-329+11-230-31-127-31-024+60-030
36Chicago BearsNFCNorth2-329+12-230-31-227+310130-51-025+70-032
37Carolina PanthersNFCSouth1-432-11-331-21-229+20-231-20-129-60-023
38Houston TexansAFCSouth1-3-131+10-3-132=00-2-132=00-1-132-40-0-128-10-027
2019 Power rankings
Cell Formulas
RangeFormula
G7:G38G7=[@[Wk 4 Rank]]-[@[Wk 5 Rank]]
J7:J38J7=[@[Wk 3 Rank]]-[@[Wk 4 Rank]]
M7:M38M7=[@[Wk 2 Rank]]-[@[Wk 3 Rank]]
P7:P38P7=[@[Wk 1 Rank]]-[@[Wk 2 Rank]]
S7:S38S7=[@[Wk 0 Rank]]-[@[Wk 1 Rank]]


I tried selecting columns E:G and Ctrl-Shift dragging them to the left. That gives me this error message.
1665512759735.png

Next I tried selecting just those three columns within the table. Dragging them results in all kinds of messes.

I tried right-clicking on E6 and selecting Insert | Table Columns to the Left. I got a new column, but the table was shifted to the right making the rows above the table misaligned.

I even tried copying the 3 columns outside the table and then back in, but got that same error message.

The only thing that sorta works is to insert 3 columns by right-clicking on the column sheet header (not the table header). Naturally, M$FT did not provide a way to insert 3 columns all at once, a feature that is in Google Sheets, so I have to do it 3 times. I can then copy the contents of the 3 columns of the previous section.

Does anyone know of a better way?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

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