欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

excel-录制宏-复制前两列,后面数据转置粘贴

程序员文章站 2024-01-03 21:21:10
...

需求:将A、B两列数据复制粘贴进sheet2中,C-后面的列数据转置粘贴进sheet2的C列,并将sheet2中的A、B两列向下填充与sheet2中的C列数据对应

excel-录制宏-复制前两列,后面数据转置粘贴

 

excel-录制宏-复制前两列,后面数据转置粘贴

 

录制的宏代码:

Sub 宏1()
'
' 宏1 宏
'
' 快捷键: Ctrl+q
'
    ActiveCell.Range("A1:B1").Select
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveSheet.Paste
    Sheets("录制宏").Select
    ActiveCell.Offset(0, 2).Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveCell.Offset(0, 2).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    ActiveCell.Offset(0, -2).Range("A1:B1").Select
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=ActiveCell.Range("A1:B182")
    ActiveCell.Range("A1:B182").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveWindow.SmallScroll Down:=6
    Sheets("录制宏").Select
    ActiveWindow.ScrollColumn = 156
    ActiveWindow.ScrollColumn = 155
    ActiveWindow.ScrollColumn = 154
    ActiveWindow.ScrollColumn = 153
    ActiveWindow.ScrollColumn = 152
    ActiveWindow.ScrollColumn = 151
    ActiveWindow.ScrollColumn = 149
    ActiveWindow.ScrollColumn = 146
    ActiveWindow.ScrollColumn = 143
    ActiveWindow.ScrollColumn = 142
    ActiveWindow.ScrollColumn = 139
    ActiveWindow.ScrollColumn = 136
    ActiveWindow.ScrollColumn = 133
    ActiveWindow.ScrollColumn = 131
    ActiveWindow.ScrollColumn = 127
    ActiveWindow.ScrollColumn = 117
    ActiveWindow.ScrollColumn = 113
    ActiveWindow.ScrollColumn = 110
    ActiveWindow.ScrollColumn = 106
    ActiveWindow.ScrollColumn = 103
    ActiveWindow.ScrollColumn = 100
    ActiveWindow.ScrollColumn = 97
    ActiveWindow.ScrollColumn = 93
    ActiveWindow.ScrollColumn = 90
    ActiveWindow.ScrollColumn = 72
    ActiveWindow.ScrollColumn = 68
    ActiveWindow.ScrollColumn = 65
    ActiveWindow.ScrollColumn = 62
    ActiveWindow.ScrollColumn = 60
    ActiveWindow.ScrollColumn = 57
    ActiveWindow.ScrollColumn = 55
    ActiveWindow.ScrollColumn = 52
    ActiveWindow.ScrollColumn = 50
    ActiveWindow.ScrollColumn = 41
    ActiveWindow.ScrollColumn = 39
    ActiveWindow.ScrollColumn = 37
    ActiveWindow.ScrollColumn = 36
    ActiveWindow.ScrollColumn = 34
    ActiveWindow.ScrollColumn = 33
    ActiveWindow.ScrollColumn = 32
    ActiveWindow.ScrollColumn = 30
    ActiveWindow.ScrollColumn = 29
    ActiveWindow.ScrollColumn = 27
    ActiveWindow.ScrollColumn = 25
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveCell.Offset(1, -2).Range("A1").Select
End Sub

录制过程:开发工具----使用相对引用----录制宏----起名----设置快捷键快速调用----确定之后进行操作,操作结束后停止录制即可。

excel-录制宏-复制前两列,后面数据转置粘贴

excel-录制宏-复制前两列,后面数据转置粘贴

上一篇:

下一篇: