在一张纸上复制静态范围，并根据单元格中的单个值粘贴到另一张纸上的动态范围 [英] Copy a static range on one sheet, and paste in a dynamic range in another sheet based on a single value in a cell

问题描述

我有三个部分来解决这个问题。我有一个单元格，在Sheet1！A1中有一个周数。我在Sheet1！B1：F1中有一个静态范围需要复制。然后，我需要将值粘贴到Sheet2偏移量的动态范围中，以行的周数。这是我为我定期使用的表格写的一个更大的宏的一部分，但我似乎有这些部分。我可能是过度简化或过度简化，但这是我目前所在。

  Sub CopyPaste（）
表格（1 ）.Range（B1：F1）。复制
 OffsetRange = Sheets（1）.Cells（1,1）.Value 
 Sheets（2）.Cells（1 + OffsetRange，1）.Paste 
 End Sub 
  

当我运行这个，它给我一个运行时错误9或运行时错误438。

任何人知道导致这些错误的原因？当我粘贴范围时，当我粘贴到位置时，单元格对象是否指向复制范围的第一个单元格？

解决方案

p>尝试它，

  Option Explicit 
 
 Sub CopyPasteOffset（）
 Dim OffsetRange As Long 
 OffsetRange = Worksheets（1）.Cells（1，1）.Value 
工作表（1）.Range（B1：F1）。复制_ 
目标：=工作表（ 2）.Cells（1 + OffsetRange，1）
 End Sub 
  

粘贴方法是Worksheet的成员，而不是Range或Cells。您可能会与 .PasteSpecial 这是一个成员的Range对象。无论如何，无需将目的地直接应用于复制命令。

I have three parts to this problem. I have a single cell with a Week number in Sheet1!A1. I have a static range in Sheet1!B1:F1 that needs to be copied. Then I need to paste the value in a dynamic range in Sheet2 offset by the week number for rows. This is part of a larger macro I am writing for a sheet I use regularly, but I seem to have those parts down. I may be either oversimplifying or oversimplifying but this is what I have currently.

Sub CopyPaste()
    Sheets(1).Range("B1:F1").Copy
    OffsetRange = Sheets(1).Cells(1,1).Value
    Sheets(2).Cells(1+OffsetRange,1).Paste
End Sub

When I run this, it either gives me a Runtime Error 9 or Runtime Error 438.

Anyone know whats causing these errors? When I paste the range, does the cells object point towards the first cell of the copied range when I paste in at the location?

解决方案

Try it as,

Option Explicit

Sub CopyPasteOffset()
    Dim OffsetRange As Long
    OffsetRange = Worksheets(1).Cells(1, 1).Value
    Worksheets(1).Range("B1:F1").Copy _
        Destination:=Worksheets(2).Cells(1 + OffsetRange, 1)
End Sub

The .Paste method is a member of Worksheet, not Range or Cells. You may have it confused with .PasteSpecial which is a member of the Range object. In any event, it is unnecessary as a destination can be applied directly to the copy command.

这篇关于在一张纸上复制静态范围，并根据单元格中的单个值粘贴到另一张纸上的动态范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！