天道酬勤,学无止境

Copy Paste VBA Range from one to another worksheet Loop and transpose the data

For A model in Excel I would like to copy and paste data from one sheet in a workbook to the other sheet in the workbook and transpose this data with the usage of a for next loop. Data from the workfile CME should be pasted in the workfile of RME. The total lines of data is 2420. However, since the ranges in the copy and paste different workbook are different and step sizes are different, I assume that i should use two different variables i and j. The first two lines of code is the manual way to do it without a loop

When I try to run this code, it will give me an error. the first to codes are an example how it should be done manually without a loop. Thereafter the loop starts. Already a lot of thanks for the person who can help me with this issue.

Example from the file: In Excel Worksheet CME: copy range J2:J10 to transfer it to worksheet RME to be pasted on B2:J2 so it is important to have a transpose function in it. For the next range I need to copy the range J12:J20 to transfer it to worksheet RME to be pasted on B3:J3 and so on, therefore I need to create a loop right? With two different stepsizes for the copy and paste ranges. Thanks a lot already!

I came up with the following formula:

Dim i As Long
Dim j As Long

Worksheets("CME").Range("J2:J10").Copy
Worksheets("RME").Range("B2").PasteSpecial Transpose:=True

Worksheets("CME").Range("J12:J20").Copy
Worksheets("RME").Range("B3").PasteSpecial Transpose:=True

For i = 11 To 2420 Step 10
    Worksheets("CME").Range (Cells(i + 11, 10)), Cells(i + 19, 10).Copy

    For j = 3 To 2420 Step 1
    Worksheets("RME").Range(Cells(j + 1, 2)).PasteSpecial Transpose:=True
    Next j

Next i

End Sub   

评论

I'm a little confused with what exactly you want to do but would this do?:

First code: Not using transpose

Sub Test()

Dim X As Long, Y As Long, Z As Long

Z = 2
For X = 2 To 2420 Step 10
    For Y = 2 To 10
        Sheets("RME").Cells(Z, Y).Value = Sheets("CME").Cells(X + (Y - 1), 10).Value
    Next Y
    Z = Z + 1
Next X

End Sub

Second code: Using transpose

Sub Test()

Dim X As Long, Z As Long
Dim RNG1 As Range, RNG2 As Range

Sheets("CME").Activate
Z = 2
For X = 2 To 2420 Step 10
    Set RNG1 = Sheets("CME").Range(Cells(X, 10), Cells(X + 9, 10))
    Set RNG2 = Sheets("RME").Cells(Z, 2)
    RNG1.Copy
    RNG2.PasteSpecial Transpose:=True
    Z = Z + 1
Next X
Sheets("RME").Activate

End Sub

Note: Copy/Pasting and transpose take much longer to execute on a larger database. To compare the two code examples above:

  • First code: 0,13 seconds
  • Second code: 5,5 seconds

受限制的 HTML

  • 允许的HTML标签:<a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • 自动断行和分段。
  • 网页和电子邮件地址自动转换为链接。

相关推荐
  • 在Excel 2007中将具有数据列的行转换为具有多行的列(Convert row with columns of data into column with multiple rows in Excel 2007)
    问题 我有一排数据如下: header1 header2 header3 header4 header5 row key datavalue1 datavalue2 datavalue3 datavalue4 datavalue5.... 所以基本上,我有一个非规范化的数据集,其中数据值在逐行的基础上可以为空,也可以不为空。 我需要将它们标准化。 IE 12345678 NULL 10 3 NULL 14 会成为: 12345678 header2 10 12345678 header3 3 12345678 header5 14 我可以通过使用粘贴特殊转换来做到这一点,但是我有成千上万的行,因此我需要确保每个行都有正确的行键。 此外,每一行都有一堆与之相关的描述,我需要将其与每个数据值一起复制。 什么是最简单的方法来转换每一行的列,以使我在一个列中包含所有非空数据值加上相关数据值引用的多行? 我需要能够透视数据集。 回答1 如果您有五个“标题”列,请输入以下公式 H1: =OFFSET($A$1,INT((ROW()-1)/5)+1,0) I1: =OFFSET($A$1,0,IF(MOD(ROW(),5)=0,5,MOD(ROW(),5))) J1: =INDEX($A$1:$F$9,MATCH(H1,$A$1:$A$9,FALSE),MATCH(I1,$A$1:$F$1
  • Excel VBA - 直到空白单元格(Excel VBA - Do Until Blank Cell)
    问题 我正在录制宏,需要一些帮助。 我想将“SalesData”工作表的 G 列中的值复制并粘贴到“结果”工作表的单元格 A2、A12、A22 等中,直到 G 列中没有更多值。 VBA 对我来说很新,我试过使用 Do/Until,但一切都崩溃了。 请你帮助我好吗? 请参阅我在下面记录的代码。 谢谢! Sub(x) Sheets("SalesData").Select Range("G2").Select Selection.Copy Sheets("Results").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("A12").Select Sheets("SalesData").Select Range("G3").Select Application.CutCopyMode = False Selection.Copy Sheets("Results").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("A22")
  • 使用 VBA 将 Excel 数据从列复制到行(Copy Excel data from columns to rows with VBA)
    问题 我对 VBA 有一点经验,我非常感谢您对这个问题的任何帮助。 从基本意义上讲,我需要将工作表 1 中的 2 列数据转换为工作表 2 中的数据行。 它目前在 Excel 中看起来像这样: 我需要它看起来像这样: 我已经编写了将标题转移到工作表 2 的代码,它工作正常。 我只是在以正确的格式传输实际值时遇到问题。 现在,我的代码主体是 ws.Range("B3").Copy ws2.Range("C2").PasteSpecial xlPasteValues ws.Range("B4").Copy ws2.Range("D2").PasteSpecial xlPasteValues ws.Range("B5").Copy ws2.Range("E2").PasteSpecial xlPasteValues ws.Range("B6").Copy ws2.Range("F2").PasteSpecial xlPasteValues 继续下去。 但是,这真的行不通,因为我正在处理的实际文档有数万个数据点。 我知道有一种方法可以使这个过程自动化,但是我尝试过的所有方法要么什么也没做,要么给出了错误 1004。 对此的任何帮助将不胜感激!! 编辑:有数百个小数据段,每段长 18 行(帧# 1 行,时间 1 行,16 个通道中的每个通道 1 行)。 我试图让它进入一个步长为 18 的循环
  • 快速堆叠列和转置(Fast Stack-Columns and Transpose)
    问题 在学习 VBA 几天后,我设法获得了一个简单的宏来从工作表中获取一些数据并将其转置到另一个,然后将列堆叠在一起。 宏 Sub pivotsourcedata() Dim HeaderSelect As Range Dim DataSelect As Range Dim ID As Range 'Variabile Declaration for Progress bar Dim x As Integer Dim MyTimer As Double For i = 1 To 7589 'Progress bar Application.StatusBar = "Progress: " & i & " of 7589: " & Format(i / 7589, "0%") 'Copy ID Range Sheets("Opps Closed FY15").Select Range("A13").Offset(i, 0).Select Set ID = Selection 'Copy Header Range Range("EX13:HA13").Select Set HeaderSelect = Selection 'Copy Data Range Range("EX13:HA13").Offset(i, 0).Select Set DataSelect = Selection
  • VBA 复制粘贴偏移量到另一个工作簿(VBA copy-paste offset to another workbook)
    问题 如果你能帮助我,我需要你的建议,因为我的 VBA 知识很差。 我有 4 列和数千行,我希望通过点击命令按钮将第一行复制粘贴到另一个工作簿中,然后第二次点击按钮覆盖之前的复制粘贴等等(在第二个工作簿我有一些公式将应用于新值,我必须检查结果)。 提前谢谢了! 这是我用来从一个工作簿复制粘贴到另一个工作簿的代码(代码正在移动到下一行,但它没有复制粘贴另一个工作簿上的值,在另一个工作簿中是相同的第一个值,所以这是我的困境): Private Sub CommandButton1_Click() ActiveCell.Offset(1, 0).Activate Workbooks("Book1.xlsm").Worksheets("Sheet2").Range("B4:E4").Copy Workbooks("Book2.xlsm").Worksheets("Sheet1").Range("B20").PasteSpecial Transpose:=True Application.CutCopyMode = False End Sub 回答1 请试试这个 Private Sub CommandButton1_Click() ActiveCell.Offset(1, 0).Activate Workbooks("Book1.xlsm").Worksheets("Sheet2")
  • 如何提高VBA宏代码的速度?(How to improve the speed of VBA macro code?)
    问题 我在编写宏方面经验不足,因此需要本社区的帮助来解决以下问题: 我的宏将在一个工作表中的垂直范围内输入的值范围复制,然后将这些值水平(转置)粘贴到另一工作表中。 理论上,它将把第一张纸中的值粘贴到第二张不包含内容的工作表的第一行中。 由于前五行包含内容,因此将值粘贴到第六行。 我在运行宏时遇到的问题是,我觉得它太慢了,因此我希望它运行得更快。 我有相同的宏在做相同的事情,但是将值粘贴到另一个工作表的第一行,并且运行完美。 因此,我最好的猜测是第二个宏运行缓慢,因为它必须开始粘贴到第六行,并且前5行中可能有一些内容需要花费很多时间才能通过该宏(有很多单元格引用其他工作簿)以确定粘贴的下一行应该在哪里。 不过,这是我最好的猜测,并且由于我几乎不了解宏,因此无法确定问题出在哪里。 我在此为您提供宏的代码,并衷心希望有人可以告诉我是什么导致我的宏变慢,并为我提供如何使其运行更快的解决方案。 我认为一种解决方案可能是该宏不应该考虑数据的前五行,而应立即开始在第6行上粘贴第一个条目。 然后在下一次在第7行,依此类推。这可能是一个解决方案,但我不知道如何以能做到这一点的方式编写代码。 感谢您抽出宝贵时间并帮助我找到解决方案,以下是代码: Sub Macro1() Application.ScreenUpdating = False Dim historyWks As Worksheet
  • 如何仅从范围中复制Excel VBA中的值?(How to copy only values in excel vba from a range?)
    问题 我正在尝试使用vba宏在Excel中将值从表复制到范围,但是我不希望表格式,仅是它的值。 我怎样才能做到这一点? 这是代码的一部分: 'Source range Set r = Sheets("Sheet1").Range("Table1") 'Destination range Set dest = Range(.Cells(linhaAtual, 1), .Cells(linhaAtual + r.Rows.Count - 1, 5)) r.Copy Destination:= dest 回答1 通过将源范围的值分配给目标范围,可以完全跳过复制命令: 'Source range Set r = Sheets("Sheet1").Range("Table1") 'Destination range Set dest = Range(.Cells(linhaAtual, 1), .Cells(linhaAtual + r.Rows.Count - 1, 5)) dest.Value = r.Value 回答2 我相信您正在寻找粘贴值的功能。 您可以记录下来,也可以使用我在下面做的事情。 (从记录中进行选择就可以使它运行的更慢,但是您不会循环播放,因此添加的只是恒定的时间)。 Selection.Copy 'Select your destination like range(
  • Excel VBA to import data from other workbook in one step
    I'm importing data from another workbook one column at a time. Is it possible, via VBA, to important several columns in one go. The columns I need to copy from and their destination are not necessarily in cronological or alphabetic order. So, column A may have to be copied to column P in the destination workbook/worksheet. This is my current code that goes back and forth between the two workbooks: Sub GetFile() Windows("Status Report Internal 2017-09-29.xlsm").Activate Range("A2:A10000").Select Selection.Copy Windows("MDC 2017.xls").Activate Sheets("Data").Select Range("A8").Select Selection
  • Excel VBA 一步导入其他工作簿中的数据(Excel VBA to import data from other workbook in one step)
    问题 我从另一个工作簿一次一列导入数据。 是否可以通过 VBA 一次性处理重要的几列。 我需要复制的列及其目的地不一定按时间顺序或字母顺序排列。 因此,可能必须将 A 列复制到目标工作簿/工作表中的 P 列。 这是我当前在两个工作簿之间来回切换的代码: Sub GetFile() Windows("Status Report Internal 2017-09-29.xlsm").Activate Range("A2:A10000").Select Selection.Copy Windows("MDC 2017.xls").Activate Sheets("Data").Select Range("A8").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Windows("Status Report Internal 2017-09-29.xlsm").Activate Range("B2:B10000").Select Selection.Copy Windows("MDC 2017.xls").Activate Range("B8").Select Selection.PasteSpecial Paste
  • 使用 VBA 在 Excel 中将保留格式的单元格值从一个单元格复制到另一个单元格(Copying the cell value preserving the formatting from one cell to another in excel using VBA)
    问题 在 excel 中,我试图将文本从一个单元格复制到另一个工作表中的另一个单元格。 源单元格包含格式化文本(粗体、下划线、不同颜色) 。 但是当我使用 VBA 将文本复制到另一个单元格时,格式丢失了。 我知道这是因为 excel 只复制文本值。 有没有办法从单元格中读取HTML 文本(而不是纯文本) ? 我用谷歌搜索了这个,没有得到任何答案。 我知道如果我们使用复制和粘贴方法,我们可以复制格式。 例如 Range("F10").Select Selection.Copy Range("I10").Select ActiveSheet.Paste 但是我想在没有复制和粘贴的情况下完成它,因为我的目标是一个合并的单元格并且与我的源单元格的大小不同。 excel VBA 中是否有可用的选项来执行此操作? 编辑:我能够使用以下代码解决它。 Range("I11").Value = Range("I10").Value For i = 1 To Range("I10").Characters.Count Range("I11").Characters(i, 1).Font.Bold = Range("I10").Characters(i, 1).Font.Bold Range("I11").Characters(i, 1).Font.Color = Range("I10")
  • Excel 宏 VBA - 如何插入复制的单元格而不是粘贴(Excel Macro VBA - How to insert copied cells instead of paste)
    问题 我有一个运行以下代码的宏,用于将数据从一个 Excel 文件复制到另一个 Excel 文件中。 目前它正在将数据复制并粘贴到第二个 Excel 文件中。 这意味着它会覆盖第二个 Excel 文件中的任何数据。 我希望它插入复制的单元格而不是粘贴工作簿中已有的数据。 我应该如何编辑第 27 行来完成这项工作? 我想我需要使用以下代码,但我不确定如何将其应用于我的原始代码。 InsertCopiedCells 这是粘贴数据的原始代码。 Const strFile As String = "E:\My Documents\file2\file\MonthlyReports\Data\file1.xlsx" 'Add the file location Dim wbCopyTo As Workbook Dim wsCopyTo As Worksheet Dim wbCopyFrom As Workbook Dim wsCopyFrom As Worksheet Set wbCopyTo = ActiveWorkbook Set wsCopyTo = ActiveSheet '------------------------------------------------------------- 'Open file with data to be copied Set
  • Copy multiple rows from one worksheet to another worksheet using macro/vba
    I've looked around the forum and played with various options but not found a clear match for my problem: My task is to copy data from a worksheet (called “workorders”) to a second worksheet (called “Assignments”). The data to be copied is from the “workorders” worksheet starting at cell range “E2, P2:S2”; and also copied from each row (same range) until column “P” is empty – (the number of rows to be copied can vary each time we need to run this macro so we can’t select a standard range) . Then pasted into the “Assignments” worksheet, starting at cell “A4”. I’ve used the forum so far to
  • 如何将数组返回到范围 excel vba(How to return array to range excel vba)
    问题 我一直在尝试从工作表中获取数据并将其放入数组,然后将数组粘贴到其他工作表中。 但是,在循环之后我的数组返回Empty 。 我需要从 For 循环返回一些东西吗? 我搜索没有找到任何想法。 Sub generate() Dim article_arr() As Variant Dim artCount As Integer Dim filter As Integer Dim RIL_itemCount As Integer 'Set PA number filter = Sheet7.Range("B1").Value RIL_itemCount = Sheet5.Cells(Sheet5.Rows.count, "A").End(xlUp).Row 'Count number article of PA selected artCount = Application.WorksheetFunction.CountIf(Sheet5.Range("R:R"), filter) 'redim array ReDim article_arr(0 To artCount) Dim j As Integer j = 0 'populate array with article number from Retail Item List For i = 0 To RIL_itemCount
  • VBA copying cell dependent on text to next blank cell on another worksheet
    I created the below and i'm an absolute noob, i literally just pieced different bits of info together to get it working and it did, until i added the selecting the cell if it has 'r' in it and moving it to 'sheet7' and now i get an (object required) error when it runs. I really need some help on this and if you are feeling generous, i would like to repeat the exercise with several other letters and sheets, so if you could demonstrate an additional one too, i'm sure i could work out the rest. Thanks in advance Sub Macro1() ' ' Macro1 Macro ' ' Range("I16").Select Selection.Copy Range("A" & Rows
  • 如何使用VBA仅将单个工作表复制到另一个工作簿(How to copy only a single worksheet to another workbook using vba)
    问题 我有一本WorkBook("SOURCE") ,其中包含约20 WorkBook("SOURCE") 。 我只想使用Excel VBA将1个特定的工作表复制到另一个Workbook("TARGET") 。 请注意,“ TARGET”工作簿尚不存在。 它应该在运行时创建。 使用的方法- 1) Activeworkbook.SaveAs <---不起作用。 这将复制所有工作表。 我只想要特定的工作表。 回答1 我有一本WorkBook(“ SOURCE”),其中包含约20张纸。 我只想使用Excel VBA将1个特定的工作表复制到另一个工作簿(“ TARGET”)。 请注意,“ TARGET”工作簿尚不存在。 它应该在运行时创建。 其它的办法 Sub Sample() '~~> Change Sheet1 to the relevant sheet '~~> This will create a new workbook with the relevant sheet ThisWorkbook.Sheets("Sheet1").Copy '~~> Save the new workbook ActiveWorkbook.SaveAs "C:\Target.xlsx", FileFormat:=51 End Sub 这将自动创建一个带有相关工作表的名为Target
  • VBA copy-paste offset to another workbook
    I would need your advice if you could help me because my VBA knowledge is very poor. I have 4 columns and thousands of rows, I would like that first row to be copied-pasted transposed in another workbook by hitting a command button, after that the second hit of button to overwrite the previous copy-paste and so on (in the second workbook I have some formulas which are gonna apply to the new values and I have to check the results). Many thanks in advance! Here is the code that I have done it to copy-paste from one workbook to the other one (the code is moving to the next row but it doesn't copy
  • VBA将依赖于文本的单元格复制到另一个工作表上的下一个空白单元格(VBA copying cell dependent on text to next blank cell on another worksheet)
    问题 我创建了下面的内容,我是一个绝对的菜鸟,我实际上只是将不同的信息拼凑在一起以使其正常工作,并且确实如此,直到我添加了选择单元格(如果其中包含“r”)并将其移动到“sheet7” ' 现在我在运行时收到(需要对象)错误。 在这方面我真的需要一些帮助,如果你觉得很慷慨,我想用其他几封信和纸重复这个练习,所以如果你也能展示一个额外的,我相信我可以解决剩下的问题。 提前致谢 Sub Macro1() ' ' Macro1 Macro ' ' Range("I16").Select Selection.Copy Range("A" & Rows.Count).End(xlUp).Offset(1).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveCell.Offset(0, 1).Select Application.CutCopyMode = False Selection.Copy Range("J20").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False
  • 使用宏/vba 将多行从一个工作表复制到另一个工作表(Copy multiple rows from one worksheet to another worksheet using macro/vba)
    问题 我环顾了论坛并尝试了各种选项,但没有找到适合我的问题的明确匹配项: 我的任务是将数据从工作表(称为“工作订单”)复制到第二个工作表(称为“作业”)。 要复制的数据来自“workorders”工作表,从单元格区域“E2, P2:S2”开始; 并且还从每一行(相同范围)复制,直到“P”列为空——(每次我们需要运行这个宏时,要复制的行数可能会有所不同,因此我们无法选择标准范围)。 然后粘贴到“作业”工作表中,从单元格“A4”开始。 到目前为止,我已经使用论坛成功复制了一行日期(从第 2 行开始)——我承认这是最简单的部分,我已经使用了各种版本的代码来实现这一点。 我还尝试了一些代码(我通过观看 youtube 剪辑和修改 http://www.youtube.com/watch?v=PyNWL0DXXtQ 发现)以允许我运行一个循环,该循环为每个所需的行重复复制过程在“workorders”工作表中,然后将数据粘贴到“assignments”工作表中 - 但这是我没有做对的地方,我认为我是在正确的路线上并且认为我不远但任何帮助都会非常有用。 下面的代码示例(前 2 个仅复制第一行,第三个示例是我尝试循环和复制多行的地方: Sub CopyTest1() ' CopyTest1 Macro 'copy data from workorders sheet 'Worksheets(
  • 使用相同的列名(不一定相同的顺序)将数据从一张工作表复制到另一张工作表(同一工作簿)(Copy data from one sheet to another (same workbook) with same column names (not necessarily same order))
    问题 我在名为“ Data ”的工作表上有两列Data和Data1 。 我在名为“ MasterData ”的工作表上有相同的列名。 我想阅读“ Data ”工作表上的内容并根据列名( Data & Data1 )复制到“ MasterData ”。 另外,假设我有 10 个数据点复制到“ MasterData ”,下次我想将这些数据保留在那里,但是通过检查第一个空单元格将新数据从工作表“ Data ”复制到“ MasterData ”(在这种情况下,将是特定列中的单元格编号 11)。 这将需要继续,因为“ MasterData ”将存储所有历史数据。 Sub CopyDatatoMasterData2() Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("MasterData").Select lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row Range("A" & lMaxRows + 1).Select ActiveSheet.Paste Sheets("Data").Select Range("B2").Select Range(Selection, Selection.End(xlDown))
  • 使用特定程序从 Excel 工作簿中提取数据以报告表(Extract data from Excel workbook with specific procedure to report sheet)
    问题 这是我的情况的问题:我的工作簿从一个月的第一天开始计算到 15 日。 (sheet 1-15) 有时会发生半个月有3周的情况。 周数从周一到周日在 de excel cels 中计算。 注意:由于使用日期,我隐藏了一些行和列。 现在我应该用 VB 建立一个月度报告,向我展示每个员工由于计算工作速度/工作而完成了多少工作。 所有的工作都是可变的,可以在每天的工作簿中选择(参见列出的工作表(1)。这个工作簿。可能我必须每周进行评估,所以 VB 仍然使用相同的 wbnew 和扩大日常工作时间的输入。我已经做了一个'部分'代码开始,但我无法处理其余的。代码应该看看有多少员工。(这个我填表(“1”) ) 的工作簿)。 它应该在每个工作日表(“1”)-表(“15)中查找: • 员工是否存在? • 我们是工作表的日期 • 它完成了哪些工作(清单中要求的工作描述 + 代码工作) • 如果工作已经存在,只需填写同一行,但在正确的日期列中,如果工作未完成,不显示工作名称,不显示工作代码 • 在工作上花费了多少时间 • 要控制计数是否正确,您可以在工作簿表 (“15”) 的 (AA) 列中查看总小时数和 cel (“S15”) of montly reportsheet(在这种情况下,两者都显示 15 小时 = ok)。 我有一个工作簿和一个发布的报告表示例。 在工作簿中