插入范围作为变量从一张纸到另一张纸 [英] Insert range as variable from one sheet to another

问题描述

我现在正在寻找比我愿意管理的解决方案更长的时间，所以这是我的问题.

I am now searching for a solution longe than i am willing to admin, so here's my problem.

我需要在第一列之后的另一张工作表中插入一个动态范围的列，该列的大部分包含2行.我无法对列进行硬编码，因此我创建了一个Application.InputBox，用户必须在其中选择范围，然后确认其选择.现在，如果我接受范围并使用rng.address制作一个味精盒，它将显示范围e.G.A $ B $.

I need to insert a dynamic range of columns cosisting mostly of 2 rows into another sheet after the first column. I can't hardcode the columns so i made a Application.InputBox where the user has to select the range and then confirms his choice. Now if i take the range and make a msg box with rng.address it shows the range e.G. A$B$.

现在，当我尝试插入范围时，根据我的尝试方式，我会遇到各种错误.

Now when i try to insert the range i get all kinds of errors depending on the way i try.

我当前的方法如下:

dim rng as Range
         retry:
Set rng = Application.InputBox("Do that and that", "Obtain Range Object", Type:=8)

 If MsgBox("Your choice " & rng.Address & " ?", vbYesNo, "Confirm") = vbYes Then
    GoTo continue:
    Else
    GoTo retry:
    End If

         continue:

'#1) i tried this:
Worksheets(templateold).Range(rng).Insert Shift:=xlToRight Worksheets(templatenew).Range(rng)

'#2) and i tried that:
Worksheets(templateold).Range(rng).Copy Worksheets(templatenew).Range(rng)

我尝试过选择拳头然后进行复制，但没有任何效果.:(

I tried it with selecting fist and then copying too but nothing works. :(

如何使用变量中存储的范围将范围插入另一张纸中?抱歉，如果我的代码段不正确，我在工作中尝试了更多操作，但家里什么都没有.

How do i use the range stored in the variable to insert the range in another sheet? Sorrys if my code snippet is bad, i tried more things at work but i don't have everything at home.

推荐答案

以下内容将复制用户选择的范围，然后将其他所有内容向右移动，将其插入到B列的另一张纸中:

The following will copy the range selected by the user and then insert it into the other sheet in Column B by shifting everything else to the right:

Sub foo()
Application.ScreenUpdating = False

Dim templateold As Worksheet: Set templateold = ThisWorkbook.Worksheets("Sheet1")
Dim templatenew As Worksheet: Set templatenew = ThisWorkbook.Worksheets("Sheet2")
'declare and set the worksheets you are working with, amend as required.
Dim rng As Range

retry:
Set rng = Application.InputBox("Do that and that", "Obtain Range Object", Type:=8)

If MsgBox("Your choice " & rng.Address & " ?", vbYesNo, "Confirm") = vbYes Then
    rng.Copy
    templateold.Range("B:B").Insert Shift:=xlToRight
Else
    GoTo retry:
End If

Application.ScreenUpdating = True
End Sub

这篇关于插入范围作为变量从一张纸到另一张纸的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！