Move Data and Copy Into Named Range

Question

I am trying to copy data from one sheet into another.

In the second sheet the entries in the rows need to move down

I would like the existing data to move down when copied into sheet 2.

I tried to use the offset function but got confused and am not sure if I am writing it correctly.

Sub MoveData()
  If ThisWorkbook.Worksheets(1).Shapes("Check Box 1").OLEFormat.Object.Value = 1 Then
     Range("c2:c10").Select
     Selection.Cut
     Sheets("Sheet2").Select
     Range("test").Select
     ActiveSheet.Paste
     Range("test").Select
  End If
End Sub

The test is meant to be a named range.

I don't know how to fix this, any help appreciated.

Answer 1

I agree with pnuts, it is not obvious what you are attempting. Below are a variety of examples. I hope that one can be adapted to your requirement. I have useed a number of alternative ways of identifying worksheets and ranges without explaining the syntax. Once you know a syntax exists, you can look it up in VB Help or on the Internet. However, it you need further explanations, I am happy to add it.

Point 1

You have used both Worksheets(1) and Worksheet("Sheet2"). My advice is: be consistent.

Starting with a new workbook, I renamed the worksheets: "Sheet1" to Sheet2", "Sheet2" to "Sheet3" and "Sheet3" to "Sheet1". I then moved the last tab to the first position. I placed "A", "B" and "C" in cell A1 of the three sheets. In the Immediate Windows, I accessed sheet 1 in three different ways and got three different worksheets:

? Worksheets(1).Cells(1,1).Value
C
? Worksheets("Sheet1").Cells(1,1).Value
A
? Sheet1.cells(1,1).value
B

OK, you are unlikely to meet anything like this in practice. But if you rename "Sheet1" as "xyz" it will still be Worksheets(1) and Sheet1. But if you then add another worksheet and move to be the last tab, it will be "Sheet1" or Sheet4 or Worksheets(4).

I do not recall that I have ever referenced a worksheet as Worksheets(1) or Sheet1.

If I need to cycle through the worksheets, I will write something like:

Option Explicit
Sub Demo1()

  Dim InxWsht As Long

  For InxWsht = 1 To Worksheets.Count
    Debug.Print "Cell A1 of worksheet """ & Worksheets(InxWsht).Name & _
                """ is " & Worksheets(InxWsht).Range("A1").Value
  Next

End Sub

to output the following to the Immediate Window:

Cell A1 of worksheet "Sheet3" is C
Cell A1 of worksheet "Sheet1" is A
Cell A1 of worksheet "Sheet2" is B

Normally I will use Worksheets("Master") because "Master" is the name known to the user.

Point 2

You write ThisWorkbook.Worksheets(1) but do not use ThisWorkbook again. If your macro is working with multiple workbooks, ThisWorkbook identifies the workbook containing the macro.

I duplicated my workbook and opened both versions. I used Alt+F8 to run a macro and got:

That is, the user can run macros from any open workbook. If you think your users will run your macro from a different workbook then you do need to be absolutely clear which workbook is active.

In macro Demo1() above I used Worksheets.Count and Worksheets(InxWsht). These refer to the active workbook. If you need to be concerned about multiple workbooks, write something like this:

Sub Demo2()

  Dim InxWsht As Long

  Debug.Print "Active workbook: " & ActiveWorkbook.Name
  Debug.Print "ThisWorkbook: " & ThisWorkbook.Name

  With ThisWorkbook
    For InxWsht = 1 To .Worksheets.Count
      With .Worksheets(InxWsht)
        Debug.Print "Cell A1 of worksheet """ & .Name & _
                    """ is " & .Range("A1").Value
      End With
    Next
  End With

End Sub

which outputs the following to the Immediate Window:

Active workbook: Book1.xls
ThisWorkbook: Book2.xls
Cell A1 of worksheet "Sheet3" is C
Cell A1 of worksheet "Sheet1" is A
Cell A1 of worksheet "Sheet2" is B  

The periods in front of .Worksheets.Count and .Worksheets(InxWsht) mean they are within the object identified in the With statement. Note that you can nest With statements and note that the Debug.Print statement in Demo2() is neater than the one in Demo1(). With statements are very useful for tidying up your code.

My point here is again: be consistent. If you need to worry about multiple workbooks then you need to worry about them for every statement not just one.

Point 3

Avoid selecting worksheets or ranges because it is slow and because it can be confusing. I have used Range.Copy which has the same effect as Copy and ActiveSheet.Paste but is faster and neater.

I filled Worksheets("Sheet1").Range(C2:C10) with a variety of values and formats:

I defined range "Test" as Worksheets("Sheet2").Range(D3:G3). I ran macro Demo3() which output the following to the Immediate Window:

Sheet2
$D$3:$G$3
Range "Test" is: Worksheets("Sheet2").Range(.Cells(3,4),.Cells(3,7))

and set Worksheets("Sheet2") to:

There is a lot in macro Demo3() but I believe if you work through it slowly you will see the effect of each statement which may not be what you expected. Hope this helps.

Sub Demo3()

  Dim ColDestLeft As Long
  Dim ColDestRight As Long
  Dim RowDestTop As Long
  Dim RowDestBot As Long

  ' Note: ranges are not just cells; they also identify the worksheet
  Debug.Print Range("Test").Worksheet.Name
  Debug.Print Range("Test").Address

  RowDestTop = Range("Test").Row
  RowDestBot = Range("Test").Row + Range("Test").Rows.Count - 1
  ColDestLeft = Range("Test").Column
  ColDestRight = Range("Test").Column + Range("Test").Columns.Count - 1

  Debug.Print "Range ""Test"" is: Worksheets(""" & _
              Range("Test").Worksheet.Name & """).Range(.Cells(" & _
              RowDestTop & "," & ColDestLeft; "),.Cells(" & RowDestBot & _
              "," & ColDestRight; "))"

  Worksheets("Sheet1").Range("C2:C10").Copy Destination:=Range("Test")
  Worksheets("Sheet1").Range("C2:C10").Copy _
                                    Destination:=Range("Test").Offset(9, 4)
  Worksheets("Sheet1").Range("C2:C10").Copy Destination:= _
                   Worksheets("Sheet2").Cells(RowDestTop + 10, ColDestLeft)

End Sub