在Excel中彼此顶替列列的转置 [英] Transposing Sets of Columns on Top of Each Other in Excel

问题描述

所以我有三组3列。每个集合始终处于相同的列顺序（SKU，Sales。Date。）

我想知道是否有一个VBA脚本或其他方法这将执行以下操作：

  1。）复制G：I 
 
 2.）粘贴到A：C 
 
 3.）复制J：L 
 
 4.）粘贴到A：C（下面的G：我的数据）
 
 5 。）复制M：O 
 
 6.）粘贴到A：C（J：L的数据）
 
 7.）重复（我想要重复每3列永远，但如果这是不可能的，我将手动输入列，如果我有
到。）
  

这是我正在寻找的视觉效果：

  Option Explicit 
 Sub moveData（）
 Dim rSource As Range，rDest As Range，r As Range 
 Dim tbl As Range，rowNum作为整数
 Const colNum = 3 
 
设置rDest =范围（A1）
设置rSource =范围（G1）
设置r = rSource 
虽然r 
设置r =范围（r，r.End（xlDown））
设置tbl =范围（r，r.Offset（0，colNum  -  1））
 tbl.Select 
 Set tbl = Range（tbl，tbl.End（xlDown）.Offset（1，0））
 tbl.Select 
 tbl.Copy 
 rDest.Select 
 rDest .PasteSpecial（xlPasteAll）
设置rDest = rDest.Offset（tbl.Rows.Count，0）
设置r = r（1，1）
 r.Select 
设置r = r.Offset（0，colNum）
 r.Select 
 Wend 
 End Sub 
  

So I have multiple sets of 3 columns. Each set is always in the same column order ("SKU", "Sales". "Date".)

I am wondering is there is a VBA script or other method that would do the following:

1.) Copy G:I

2.) Paste into A:C

3.) Copy J:L

4.) Paste into A:C (Underneath G:I's data)

5.) Copy M:O

6.) Paste into A:C (underneath J:L's data)

7.) Repeat (I would like it to repeat every 3 columns forever, but if that's not possible I'll manually input the columns if I have
to.)

This is a visual of what I'm looking for: http://i.imgur.com/AagLIm8.png

I also uploaded the workbook in case you need it for reference: https://www.dropbox.com/s/wea2nr4xbfo4934/Workbook.xlsx?dl=0

Thanks for the help!

解决方案

The code below does what you want, and I've included some ".select" lines to help you understand. I suggest you step through it to become clear, as in the animated gif. Then, remove all the ".select" lines of code.

Option Explicit
Sub moveData()
Dim rSource As Range, rDest As Range, r As Range
Dim tbl As Range, rowNum As Integer
Const colNum = 3

Set rDest = Range("A1")
Set rSource = Range("G1")
Set r = rSource
While r <> ""
    Set r = Range(r, r.End(xlDown))
    Set tbl = Range(r, r.Offset(0, colNum - 1))
    tbl.Select
    Set tbl = Range(tbl, tbl.End(xlDown).Offset(1, 0))
    tbl.Select
    tbl.Copy
    rDest.Select
    rDest.PasteSpecial (xlPasteAll)
    Set rDest = rDest.Offset(tbl.Rows.Count, 0)
    Set r = r(1, 1)
    r.Select
    Set r = r.Offset(0, colNum)
    r.Select
Wend
End Sub

这篇关于在Excel中彼此顶替列列的转置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！