Code to copy then paste certain columns as values

[Reecenorman1996]

I recorded the below macro, but i need the range to be my active sheet but i cant work out who to do it , any help would be much apprenticed. the table heading remain the same for the sheet name will change

Sub Copypasteasvalue()
'
' Copypasteasvalue Macro
' Copy and paste as a value
'

'
    Range("Outbound_table1454647[Total Connected Calls]").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("Outbound_table1454647[Call Time]").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("Outbound_table1454647[Notes]").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("Outbound_table1454647[CV Booked]").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("Outbound_table1454647[[CV Done]:[CV booked per day ]]").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("Outbound_table1454647[[Pre-CV]:[Pre-CV per day ]]").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Range("Outbound_table1454647[Instr.]").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("Outbound_table1454647[FS Appts Booked]").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("Outbound_table1454647[FS Done]").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Range("Outbound_table1454647[[FS approved (Admin) ]:[FS Signups]]").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("Outbound_table1454647[Convy. Ref]").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("Outbound_table1454647[Convy. Signups]").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Range("Outbound_table1454647[Refurb Ref]").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("Outbound_table1454647[Other 3rd Party]").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("Outbound_table1454647[[Viewings Booked]:[Online 5 Star Reviews]]"). _
        Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
End Sub

Table below for reference

Days Worked

CV booked per day

Total Connected Calls

Connect Call Target

Calls Vs Target

Call Time

Call Time Target

Yellow Cards

Yellow Cards VS Target of Zero

Notes

Rolling Notes Target

Notes VS Target

CV Booked

Rolling MA Booked Target

CV Booked VS Target

CV Done

CV booked per day

CV Done Target (for month)

CV Done VS Target

Pre-CV

Pre-CV per day

Pre-CV Target

Pre-CV Vs Target

Instr.

Instr. Target (for month)

Instr. VS Target

FS Appts Booked

FS Appts Booked Target

FS Appts Booked VS Target

FS Done

FS Done Target

FS Done VS Target

FS approved (Admin)

FS Confirmed (Admin)

FS Completed (Admin)2

FS Signups

FS Signups Target

FS Signups VS Target

Convy. Ref

Convy. Ref Target

Convy. Ref VS Target

Convy. Signups

Convy. Signups Target

Convy. Signups VS Target

Refurb Ref

Refurb Target (1 every 5 days worked)

Refurb Ref VS Target

Other 3rd Party

Other 3rd Party Target (1 every 5 days worked)

Other 3rd Party VS Target

Viewings Booked

Viewings Done

Intro Added Lettings

Sale/Let Agreed

Online 5 Star Reviews

% of 5 Star Reviews Target 4

Total %

MA Points = 5

FS / Cov Signup = 5

FS / Conv Ref = 1

Refurb Ref = 3

Other 3rd Party = 2

100 Calls (Per day) = 5

5 Star Reviews = 3

Viewings Done = 1pt per 5

Total Points

Points Per Day

15.02​

1.33​

1547​

751

206.0%​

44:32:00​

30.04

-

100%​

732​

1547​

47%​

20​

45

44%​

16​

1.3315579​

17

94%​

1​

0​

30

3%​

6​

4

150%​

0​

15.02​

0%​

0​

12.767​

0%​

0​

0​

0​

0​

3.19175​

0%​

0​

15.02​

0%​

0​

0​

0%​

0​

3.004​

0%​

0​

3.004​

0%​

67​

16​

7​

0​

0

0%​

43%​

100

-

-

-

-

77

0​

3​

181

12

13.64​

2.13​

1458​

1,364

106.9%​

38:33:00​

27.28

-

100%​

878​

1458​

60%​

29​

41

71%​

22​

2.1260997​

25

89%​

21​

2​

27

77%​

5​

6

91%​

0​

13.64​

0%​

0​

11.594​

0%​

0​

0​

0​

0​

2.8985​

0%​

0​

13.64​

0%​

0​

7.51​

0%​

0​

2.728​

0%​

0​

2.728​

0%​

92​

25​

32​

0​

0

0%​

40%​

145

-

-

-

-

73

0​

5​

223

16

19.2​

1.30​

1640​

1,920

85.4%​

27:07:00​

38.40

-

100%​

374​

1640​

23%​

25​

96

26%​

19​

1.3020833​

21

89%​

1​

0​

38

3%​

3​

5

63%​

0​

19.2​

0%​

0​

16.32​

0%​

0​

0​

0​

0​

4.08​

0%​

0​

19.2​

0%​

0​

0​

0%​

0​

3.84​

0%​

0​

3.84​

0%​

43​

16​

44​

1​

0

0%​

26%​

125

-

-

-

-

82

0​

3​

210

11

18​

1.22​

1677​

1,800

93.2%​

25:06:00​

36.00

-

100%​

274​

1677​

16%​

22​

54

41%​

22​

1.2222222​

19

118%​

8​

0​

36

22%​

4​

6

73%​

0​

18​

0%​

0​

15.3​

0%​

0​

0​

0​

0​

3.825​

0%​

0​

18​

0%​

0​

9.6​

0%​

0​

3.6​

0%​

0​

3.6​

0%​

39​

11​

20​

0​

0

0%​

31%​

110

-

-

-

-

84

0​

2​

196

11

4.18​

2.63​

343​

418

82.1%​

5:29:00​

8.36

-

100%​

145​

343​

42%​

11​

17

66%​

7​

2.6315789​

9

75%​

0​

0​

8

0%​

4​

2

229%​

0​

4.18​

0%​

0​

3.553​

0%​

0​

0​

0​

0​

0.88825​

0%​

1​

4.18​

418%​

0​

0​

0%​

1​

0.836​

120%​

1​

0.836​

120%​

10​

3​

6​

0​

0

0%​

83%​

55

-

1

3

2

17

0​

1​

79

19

12.64​

1.03​

718​

897

80.0%​

22:26:00​

25.28

-

100%​

427​

718​

59%​

13​

25

51%​

12​

1.028481​

11

109%​

15​

1​

25

59%​

2​

3

67%​

2​

12.64​

16%​

1​

10.744​

9%​

0​

0​

0​

0​

2.686​

0%​

0​

12.64​

0%​

0​

9​

0%​

0​

2.528​

0%​

0​

2.528​

0%​

27​

16​

68​

0​

0

0%​

37%​

65

-

2

-

-

36

0​

3​

106

8

20.7​

0.29​

1716​

2,070

82.9%​

66:40:00​

41.40

-

100%​

621​

1716​

36%​

6​

62

10%​

3​

0.2898551​

5

59%​

0​

0​

41

0%​

0​

1

0%​

0​

20.7​

0%​

0​

17.595​

0%​

0​

0​

0​

0​

4.39875​

0%​

0​

20.7​

0%​

0​

6.32​

0%​

0​

4.14​

0%​

0​

4.14​

0%​

40​

14​

76​

0​

0

0%​

19%​

30

-

-

-

-

86

0​

3​

119

6

9.88​

1.01​

915​

988

92.6%​

25:22:00​

19.76

-

100%​

510​

915​

56%​

10​

30

34%​

4​

1.0121457​

9

47%​

14​

1​

20

71%​

0​

1

0%​

1​

19.76​

5%​

1​

16.796​

6%​

0​

0​

0​

0​

4.199​

0%​

0​

9.88​

0%​

0​

10.35​

0%​

0​

1.976​

0%​

0​

1.976​

0%​

32​

15​

161​

1​

1

25%​

27%​

50

-

1

-

-

46

3​

3​

103

10

0​

0.00​

0​

-

0.0%​

0:00:00​

-

-

100%​

0​

0​

0%​

0​

-

0%​

0​

0​

-

0%​

0​

0​

-

0%​

0​

-

0%​

0​

0​

0%​

0​

0​

0%​

0​

0​

0​

0​

0​

0%​

0​

0​

0%​

0​

4.94​

0%​

0​

0​

0%​

0​

0​

0%​

0​

0​

0​

0​

0

0%​

7%​

-

-

-

-

-

-

0​

0​

-

-

10.86​

1.10​

914​

662

138.0%​

25:17:00​

21.72

-

100%​

623​

914​

68%​

12​

22

55%​

8​

1.1049724​

10

78%​

45​

4​

22

207%​

0​

2

0%​

1​

10.86​

9%​

0​

9.231​

0%​

0​

0​

0​

0​

2.30775​

0%​

0​

10.86​

0%​

0​

0​

0%​

0​

2.172​

0%​

0​

2.172​

0%​

40​

14​

57​

0​

0

0%​

44%​

60

-

1

-

-

46

0​

3​

110

10

14.54​

2.20​

1500​

1,454

103.2%​

35:04:00​

29.08

-

100%​

943​

1500​

63%​

32​

44

73%​

24​

2.2008253​

27

88%​

31​

2​

29

107%​

9​

6

150%​

0​

29.08​

0%​

0​

24.718​

0%​

43​

38​

23​

0​

6.1795​

0%​

2​

14.54​

727%​

0​

4.94​

0%​

0​

2.908​

0%​

1​

2.908​

34%​

34​

15​

93​

0​

0

0%​

96%​

160

-

2

-

2

75

0​

3​

242

17

16.7​

0.60​

1016​

1,186

85.7%​

29:48:00​

33.40

-

100%​

1219​

1016​

120%​

10​

33

30%​

9​

0.5988024​

9

106%​

44​

3​

33

132%​

1​

2

44%​

5​

16.7​

30%​

2​

14.195​

14%​

0​

0​

0​

0​

3.54875​

0%​

0​

16.7​

0%​

0​

7.27​

0%​

0​

3.34​

0%​

0​

3.34​

0%​

75​

30​

190​

0​

1

25%​

44%​

50

-

5

-

-

51

3​

6​

115

7

18.9​

0.05​

873​

1,229

71.1%​

30:44:00​

37.80

-

100%​

1021​

873​

117%​

1​

38

3%​

1​

0.0529101​

1

118%​

76​

4​

38

201%​

0​

0

0%​

1​

18.9​

5%​

0​

16.065​

0%​

244​

200​

111​

0​

4.01625​

0%​

0​

18.9​

0%​

0​

0​

0%​

0​

3.78​

0%​

0​

3.78​

0%​

8​

1​

24​

0​

0

0%​

41%​

5

-

1

-

-

44

0​

0​

50

3

15.7​

0.38​

1280​

1,570

81.5%​

44:55:00​

31.40

-

100%​

435​

1280​

34%​

6​

47

13%​

3​

0.3821656​

5

59%​

2​

0​

31

6%​

1​

1

133%​

2​

15.7​

13%​

0​

13.345​

0%​

0​

0​

0​

0​

3.33625​

0%​

0​

15.7​

0%​

0​

9.45​

0%​

0​

3.14​

0%​

0​

3.14​

0%​

67​

20​

26​

1​

0

0%​

29%​

30

-

2

-

-

64

0​

4​

100

6

14.7​

0.27​

1163​

735

158.2%​

31:15:00​

29.40

-

100%​

787​

1163​

68%​

4​

29

14%​

2​

0.2721088​

3

59%​

1​

0​

29

3%​

0​

1

0%​

2​

29.4​

7%​

1​

24.99​

4%​

0​

0​

0​

0​

6.2475​

0%​

0​

14.7​

0%​

0​

7.27​

0%​

0​

2.94​

0%​

0​

2.94​

0%​

70​

17​

76​

0​

3

75%​

28%​

20

-

2

-

-

58

9​

3​

93

6

13​

1.85​

1005​

1,300

77.3%​

19:10:00​

26.00

-

100%​

344​

1005​

34%​

24​

39

62%​

24​

1.8461538​

20

118%​

2​

0​

26

8%​

6​

6

100%​

5​

13​

38%​

4​

11.05​

36%​

5​

4​

3​

0​

2.7625​

0%​

1​

13​

1300%​

0​

7.85​

0%​

0​

2.6​

0%​

0​

2.6​

0%​

35​

14​

34​

2​

0

0%​

125%​

120

-

6

-

-

50

0​

3​

179

14

17.8​

1.24​

1601​

1,780

89.9%​

29:30:00​

35.60

-

100%​

501​

1601​

31%​

22​

53

41%​

15​

1.2359551​

19

17​

1​

36

48%​

5​

4

133%​

1​

17.8​

6%​

0​

15.13​

0%​

0​

0​

0​

0​

3.7825​

0%​

0​

17.8​

0%​

0​

7.35​

0%​

0​

3.56​

0%​

0​

3.56​

0%​

12​

4​

4​

0​

0

0%​

30%​

110

-

1

-

-

80

0​

1​

192

11

12.9​

1.55​

1275​

1,290

98.8%​

20:21:00​

25.80

-

100%​

698​

1275​

55%​

20​

39

52%​

15​

1.5503876​

17

88%​

5​

0​

26

19%​

2​

4

53%​

6​

12.9​

47%​

2​

10.965​

18%​

0​

0​

0​

3​

2.74125​

109%​

0​

12.9​

0%​

0​

0​

0%​

0​

2.58​

0%​

0​

2.58​

0%​

38​

15​

14​

1​

0

0%​

43%​

100

15

6

-

-

64

0​

3​

188

15

19.28​

1.14​

1853​

1,928

96.1%​

39:25:00​

38.56

-

100%​

962​

1853​

52%​

22​

58

38%​

14​

1.1410788​

19

75%​

18​

1​

39

47%​

3​

4

86%​

1​

19.28​

5%​

0​

16.388​

0%​

0​

0​

0​

0​

4.097​

0%​

1​

19.28​

1928%​

1​

6.45​

16%​

0​

3.856​

0%​

0​

3.856​

0%​

63​

30​

134​

1​

0

0%​

163%​

110

5

2

-

-

93

0​

6​

216

11

21​

0.67​

1826​

2,100

87.0%​

67:30:00​

42.00

-

100%​

878​

1826​

48%​

14​

63

22%​

7​

0.6666667​

12

59%​

5​

0​

42

12%​

1​

2

57%​

0​

21​

0%​

0​

17.85​

0%​

0​

0​

0​

0​

4.4625​

0%​

0​

21​

0%​

0​

9.64​

0%​

0​

4.2​

0%​

0​

4.2​

0%​

134​

63​

494​

2​

0

0%​

26%​

70

-

-

-

-

91

0​

13​

174

8

0.5​

0.00​

0​

50

0.0%​

0:00:00​

1.00

-

100%​

15​

0​

0%​

0​

2

0%​

0​

0​

-

0%​

0​

0​

1

0%​

0​

-

0%​

0​

0.5​

0%​

0​

0.425​

0%​

0​

0​

0​

0​

0.10625​

0%​

0​

0.5​

0%​

0​

10.5​

0%​

0​

0.1​

0%​

0​

0.1​

0%​

4​

0​

0​

0​

0

0%​

7%​

-

-

-

-

-

-

0​

0​

-

-

0.5​

0.00​

0​

50

0.0%​

0:00:00​

1.00

-

100%​

9​

0​

0%​

0​

2

0%​

0​

0​

-

0%​

0​

0​

1

0%​

0​

-

0%​

0​

0.5​

0%​

0​

0.425​

0%​

0​

0​

0​

0​

0.10625​

0%​

0​

0.5​

0%​

0​

0.25​

0%​

0​

0.1​

0%​

0​

0.1​

0%​

3​

0​

0​

0​

0

0%​

7%​

-

-

-

-

-

-

0​

0​

-

-

289.64​

1.00​

24320​

87%​

28:33:22​

-

12396​

303​

227​

306​

52

11​

3​

5​

1​

1​

2​

933​

339​

1560​

9​

5​

1,515.00

20

32

3

4

1,216

15​

68​

2872.8​

201

21​

 

[My Aswer Is This]

I see your post is nearly two days old with no answers so:
I must say I like for users to explain in detail what you're attempting to do.
Trying to read all this code and understand what you're attempting to do is too much for me. So could you just explain what you're wanting to achieve. Thanks.

 

[Peter_SSs]

the table heading remain the same for the sheet name will change

Does that also mean that the table name changes and may not be "Outbound_table1454647"?

Could there be more than one formal table on the active sheet?

It also looks like your columns may have changed position/order since you recorded that macro. Can you explain any more about that?

BTW, when posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

 

[Reecenorman1996]

Does that also mean that the table name changes and may not be "Outbound_table1454647"?

Could there be more than one formal table on the active sheet?

It also looks like your columns may have changed position/order since you recorded that macro. Can you explain any more about that?

BTW, when posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Yes table name will change but there will always be 1 table on the active sheet , columns will always stay the same as well , and thank you for the tags i will include next time.

i have 1 master sheet for all the formulas on which uses vlookup to pull correct data from a weekly report , i then copy that master and save as a date then remove all formulas in the specific columns so when i do next weeks report it doesn't overwrite the previous weeks

 

[Peter_SSs]

Thanks for the extra information.
There are a couple of things that don't seem quite right to me

1. When I copy your sample data and turn it into a table with the correct name, your code does not run correctly on it. There are several instances where column labels in your code have a space at the end but the data copied across does not. Example
   
   

   Range("Outbound_table1454647[[CV Done]:[CV booked per day ]]").Select

   
   
2. In your code, the first 4 ranges that you process are all included in the 5th range
   

   Range("Outbound_table1454647[[CV Done]:[CV booked per day ]]").Select

   so it seems like some doubling up that is not needed

In any case, give this a try with a copy of your workbook after checking the column names in the sheet with those in the code looking out for any actual trailing spaces.

Sub Test()
  Dim v As Variant
  Dim tblName As String
  
  Const ColNames As String = "[[CV Done]:[CV booked per day]]|[[Pre-CV]:[Pre-CV per day]]|[Instr.]|[FS Appts Booked]|" _
                            & "[FS Done]|[[FS approved (Admin)]:[FS Signups]]|[Convy. Ref]|[Convy. Signups]|[Refurb Ref]|" _
                            & "[Other 3rd Party]|[[Viewings Booked]:[Online 5 Star Reviews]]"
                            
  tblName = ActiveSheet.ListObjects(1).Name
  For Each v In Split(ColNames, "|")
    Range(tblName & v).Value = Range(tblName & v).Value
  Next v
End Sub