-1

I am trying to transfer selected data from one worksheet to another via a Transfer macro. Every time I try to do so, I get the following message in Debug:

Sub Transfer Macro
    ActiveCell.Offset(-10, -7).Range("A1").Select

    Selection.Copy

    Sheets("Data Presentation Template").Select

    ActiveCell.Offset(-25, -6).Range("A1").Select

    ActiveSheet.Paste
End Sub

Is there a better way to transfer/ mirror the data from a data entry sheet to a presentation sheet with out macro?

HackSlash
  • 4,944
  • 2
  • 18
  • 44
Knowledgeseeker
  • 1
  • 1
    See [how to avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). It's likely due to your use of `ActiveCell` after you switch worksheets. – BruceWayne Dec 12 '18 at 16:01
  • `ActiveCell.Offset(-10,-7).Range("A1")`... that's weird. What are you trying to do here? Whatever it is it can probably be done with a single line of code like `Range("A1").Copy Destination:=Sheets("Data Presentation Template").Range("A1")` – JNevill Dec 12 '18 at 16:16
  • I have a form which is used for data entry, then I want the same data to be mirror onto a presentation sheet automatically. – Knowledgeseeker Dec 12 '18 at 16:22
  • I used "=" to mirror the data, which worked, but when I run a Macro to reset the form and the presentation sheet, the formula bar gets wiped out too. – Knowledgeseeker Dec 12 '18 at 16:24

1 Answers1

1

Your ActiveCell.Offset(r, c) commands use negative numbers. These will depend on whether the currently active cell is in a position where the negative offsets don't try to .Select a cell that is either off the worksheet to the left of column A or a cell that is above row 1.

ActiveCell.Offset(-10, -7).Range("A1").Select

This demands that the ActiveCell is at least 10 rows down and 7 columns right from A1; specifically K8 or to the right/down of K8. Anything closer to A1 will produce an error since you are trying to select a cell that is off the worksheet.

ActiveCell.Offset(-25, -6).Range("A1").Select

The same for this statement but the minimal distance for the ActiveCell from A1 would be Z7.

The ActiveCell property changes every time you select another cell. It cannot be relied upon to 'automatically transfer' data between worksheets unless you check to ensure that the ActiveCell is where you want it on each worksheet before running your sub procedure.

Any further recommendations would require specific worksheet names and ranges for the source and target of the copy & paste operation.

The .Range("A1") in ActiveCell.Offset(-10, -7).Range("A1").Select simply means the top-left cell in the ActiveCell.Offset(-10, -7) range. It is likely a 'hangover' from relative positioned macro recording.