基于单元格值的行数(带复制的循环) [英] Number of rows based on the cell value (loop with copy)

问题描述

我想将我的地址列表与首页上单元格中显示的数字同步.情况如下:

在单元格D41中，我有公寓的数量.现在，当我打开地址列表"工作表时，我希望第一行立即向下复制40次(标有红色).我知道，可以将其描述为一个循环，这就是为什么我尝试以下代码的原因:

1. 原始来源:

解决方案

因为粘贴的目的地始终是A3: Destination:= ws2.Range("A3")始终粘贴在A3中(单元格D15次).

以下内容将复制范围A2:K2并将其粘贴到A3和以下 D15 单元格中.

 设置rg = ws1.Range("D15")设置rg2 = ws2.Range("A2:K2")rg2.Copy目标:= ws2.Range("A3").Resize(RowSize:= rg.Value) 

I would like to sync my address list with the number shown in the cell at the front sheet. The situation looks as follows:

In the cell D41 I have the number of flats. Now, when I open the "Address list" sheet I want to have the first row instantly copied 40 times down (marked with red). I know, that it can be described as a loop, this is why I tried this code:

1. Original source here:

Relocation of multiple images with ID changing

 Private Sub AddressList()
 Dim i As Long
 Dim rg As Range, rg2 As Range

 Dim ws1 As Worksheet, ws2 As Worksheet

 Set ws1 = ThisWorkbook.Sheets("Frontsheet")
 Set ws2 = ThisWorkbook.Sheets("Address list")

 Set rg = ws1.Range("D15").Value


 For i = 1 To rg
 Set rg2 = ws2.Range("B2:R2")
 With rg2.Offset(i - 1, 0)
    .Top = .Top
    .Left = .Left

 End With

 Next I

 End Sub

Here I am getting an error 424: Object required

Another code, which I tried is:

 Sub AddressList()
  Dim i As Long
  Dim LastrowE As Long
  Dim rng As Range
  Dim rg As Range, rg2 As Range

  Dim ws1 As Worksheet, ws2 As Worksheet

  Set ws1 = ThisWorkbook.Sheets("Frontsheet")
  Set ws2 = ThisWorkbook.Sheets("Fibre drop release sheet")

  Set rg = ws1.Range("D32")
  Set rg2 = ws2.Range("A2:k2")

  For i = 1 To rg

  With rg2.offset(i - 1, 0)
     rg2.Copy _
       Destination:=ws2.Range("A3")
  End With

  Next I

  End Sub

it works, but the row is copied only once. I want to have it copied 41 times as states in the Frontshet.D15 cell. How can I do this?

解决方案

Because your destination to paste is always A3: Destination:=ws2.Range("A3") it always pastes in A3 (cell D15 times).

The following will copy range A2:K2 and paste it into A3 and the following D15 cells.

Set rg = ws1.Range("D15")
Set rg2 = ws2.Range("A2:K2")

rg2.Copy Destination:=ws2.Range("A3").Resize(RowSize:=rg.Value)

这篇关于基于单元格值的行数(带复制的循环)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！