使用Excel VBA复制粘贴行是否简单? [英] Is this simple to copy pasting rows with Excel VBA?

问题描述

我在3个excel工作表中都有这样的列:

I have columns in 3 excel sheets like this:

Sheet1
ColA   ColB
5        4
5        5
45       56
56       56

Sheet2
ColA   ColB
53      24
55      55

Sheet3
ColA   ColB
45       56
56       56
3        4

我想将粘贴的列从工作表2和3复制到工作表1，我不确定行号，因为行号可以根据数据进行更改.

I want to copy paste columns from sheet 2 and 3 to sheet 1 and I am not sure of the row numbers as they can change based on the data.

谁能告诉我与此相关的宏代码，而不必确定Excel工作表中的最后一个数据行.

Can anyone tell me the macro code to this without being sure of last data row in excel sheet.

非常感谢您的建议.

推荐答案

如果只想移动值，则需要执行以下操作.如果您也要移动格式，请询问.

If you just want to move the values, the following is what you are after. If you want to move the formatting as well, ask.

Sub CopyToSheet1()

  Dim Row1Max As Long
  Dim Row1Next As Long
  Dim Row23Max As Long
  Dim Values() As Variant

  ' Find bottom rows of sheets 1 and 2
  ' These statements position a virtual cursor to the bottom of column 1
  ' and then move up until they find data.  For Sheet 1 it adds one because
  ' it needs the first blank row 
  Row1Next = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1
  Row23Max = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
  ' Extract data from sheet 2
  Values = Worksheets("Sheet2").Range("A1:B" & Row23Max).Value
  ' Drop into  sheet 1
  Row1Max = Row1Next + Row23Max - 1
  Worksheets("Sheet1").Range("A" & Row1Next & ":B" & Row1Max).Value = Values
  Row1Next = Row1Max + 1
  ' Find bottom row of sheet3
  Row23Max = Worksheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Row
  ' Extract data from sheet 3
  Values = Worksheets("Sheet3").Range("A1:B" & Row23Max).Value
  ' Drop into  sheet 1
  Row1Max = Row1Next + Row23Max - 1
  Worksheets("Sheet1").Range("A" & Row1Next & ":B" & Row1Max).Value = Values
  End Sub

这篇关于使用Excel VBA复制粘贴行是否简单?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！