How to cut a selection of rows and paste into another workbook & repeat

I have a spreadsheet with 15000 rows in one column. I want to create a script to colour code each cell descending down the spreadsheet. What I need is:

我在一列中有一个包含15000行的电子表格。我想创建一个脚本来对电子表格中下降的每个单元格进行颜色编码。我需要的是：

• First 10 cells in RED
• RED中的前10个细胞
• Next 5 cells in BLUE
• 接下来是BLUE的5个细胞
• Next 5 cells in GREEN
• 接下来是绿色的5个细胞
• Repeat this for all cells descending down from A1 to A1500...
• 对于从A1下降到A1500的所有单元格重复此操作...

Any quick pointers? They had an admin girl doing this manually for three days :/

有什么快速指针吗？他们有一个管理员女孩手动这样做了三天：/

-----EDIT

- - -编辑

Thanks guys for the responses, extremely helpful!

谢谢大家的回复，非常有帮助！

But - It turns out I have misunderstood the full scale of what is. Quite complex now that I realise what is needed.

但是 - 事实证明我误解了它的全部规模。现在很复杂，我意识到需要什么。

What we have is: four workbooks in one spreadsheet (tier 1a, tier 1b, tier 1c, sheet1)

我们拥有的是：一个电子表格中的四个工作簿（第1a层，第1b层，第1c层，第1层）

The script needs to:

该脚本需要：

• Cut the first 10 cells in tier 1 and paste into sheet1 column A;
• 切割第1层中的前10个单元格并粘贴到sheet1列A中;
• Cut the first 5 cells in tier 2 and paste into sheet1 column A;
• 切割第2层中的前5个单元格并粘贴到sheet1列A中;

• Cut the first 5 cells in tier 3 and paste into sheet1 column A;

  切割第3层中的前5个单元并粘贴到sheet1列A中;

• Repeat in descending order for all cells in each workbook - so the end result will have 10-5-5 10-5-5 10-5-5 values etc. in sheet1 column A

  对每个工作簿中的所有单元格按降序重复 - 因此最终结果将在sheet1列A中具有10-5-5 10-5-5 10-5-5值等

Any help would be greatly appreciated :) otherwise manual it is.. please save my sanity

任何帮助将不胜感激:)否则手动它是..请保存我的理智

4 个解决方案

#1

Here is a simple macro to do it

这是一个简单的宏来做到这一点

Sub colorRBGRow()

   For X = 0 To 14980 step 20
    Range("A" & X + 1 & ":A" & X + 10).Interior.ColorIndex = 3
    Range("A" & X + 11 & ":A" & X + 15).Interior.ColorIndex = 5
    Range("A" & X + 16 & ":A" & X + 20).Interior.ColorIndex = 4

    Next X
End Sub

#2

You can use conditional formatting: eg for the first color (assuming your data begin on row 2) use the formula:

您可以使用条件格式：例如，对于第一种颜色（假设您的数据从第2行开始），请使用以下公式：

=AND(MOD(ROW()-1,20)>0,MOD(ROW()-1,20)<11) 

Next color:

下一个颜色：

=AND(MOD(ROW()-1,20)>10,MOD(ROW()-1,20)<16)

Last one is trickier:

最后一个比较棘手：

=OR(MOD(ROW()-1,20)=0,AND(MOD(ROW()-1,20)>15,MOD(ROW()-1,20)<20))

#3

Try using this formula and conditional formatting to colour the cells:

尝试使用此公式和条件格式为单元格着色：

=CHOOSE(INDEX(MOD(INT((ROW(RC)-1)/5),4),1,1)+1,1,1,2,3)

Now I use R1C1 mode, rather than A1 mode, so just replace the RC with the cell you are formatting and then copy paste the formula into the other cells.

现在我使用R1C1模式而不是A1模式，所以只需将RC替换为您正在格式化的单元格，然后将公式复制粘贴到其他单元格中。

In any case this formula returns the number 1, 2, or 3 representing the colours RED, BLUE, and GREEN.

在任何情况下，此公式返回表示颜色RED，BLUE和GREEN的数字1,2或3。

I then formatted every cell RED, and then it's just a case of applying two conditional formats for BLUE and GREEN:

然后我将每个单元格格式化为RED，然后只是为BLUE和GREEN应用两种条件格式：

=CHOOSE(INDEX(MOD(INT((ROW(RC)-1)/5),4),1,1)+1,1,1,2,3)=2

And:

和：

=CHOOSE(INDEX(MOD(INT((ROW(RC)-1)/5),4),1,1)+1,1,1,2,3)=3

Here's my result:

这是我的结果：

#4

You can actually auto-fill colors. You can do this with VBA or manually.

您实际上可以自动填充颜色。您可以使用VBA或手动执行此操作。

VBA:

VBA：

Sub Macro1()
    Range("A1:A20").Select
    Selection.AutoFill Destination:=Range("A1:A15000"), Type:=xlFillDefault
End Sub

Do this on a blank sheet - you can copy-paste this color format onto any cells you want.

在空白纸上执行此操作 - 您可以将此颜色格式复制粘贴到所需的任何单元格上。

You can use an autofill type of xlFillFormats if you don't want to do any copy-pasting but it will override any other (non-color related) formats as well:

如果您不想进行任何复制粘贴，则可以使用自动填充类型的xlFillFormats，但它也将覆盖任何其他（非颜色相关）格式：

Sub Macro2()
    Range("A1:A10").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("A11:A15").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 12611584
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("A16:A20").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("A1:A20").Select
    Selection.AutoFill Destination:=Range("A1:A109"), Type:=xlFillFormats
End Sub