1

I'm writing a code and have particular problem with reading data ranges from different sheets. The problematic partition of the code is:

Dim INP, TMP as worksheet
Set INP = Sheets("Input")
Set TMP = Sheets("Temp")
TMP.Range(Cells(1,1),Cells(1,3)).Value = INP.Range(Cells(1,1),Cells(1,3)).Value

Which, instead of writing the values from the range on input sheet to the range on Temp sheet, gives an

error 1004 (Method range of worksheet failed).

Can you guys help me solve this?

braX
  • 11,506
  • 5
  • 20
  • 33
Martin Panacek
  • 11
  • 1
  • 3
    Qualify your `Cells` references with `TMP.` or `INP.` as well. They are not implied. And Dim your `INP` as a worksheet too. The way you have it now, it is a variant. – braX Jan 26 '20 at 23:45
  • 1
    Agree with @braX, reference to the range needs to be changed to `TMP.Range(TMP.Cells(1, 1), TMP.Cells(1, 3)).Value = INP.Range(INP.Cells(1, 1), INP.Cells(1, 3)).Value`, or like this `TMP.Range("A1:C1").Value = INP.Range("A1:C1").Value` – Vasily Jan 26 '20 at 23:55
  • Thanks @braX, that helped. Both of the sheet variables are declared in one row, so that should be fine. To Vasily, your second option is not possible, since i need to use variable rows and columns in the ranges, this was just a simplified case. Nevertheless, thanks to both of you! – Martin Panacek Jan 26 '20 at 23:59
  • https://stackoverflow.com/questions/28439376/what-is-the-default-scope-of-worksheets-and-cells-and-range/28439984#28439984 – Tim Williams Jan 27 '20 at 00:01
  • 3
    No, `Dim INP, TMP as worksheet` is not the same as `Dim INP as worksheet, TMP as worksheet` – braX Jan 27 '20 at 00:07
  • Did not know that, thank's. Does not affect the code though..., but good to know. – Martin Panacek Jan 27 '20 at 00:13
  • 1
    Did you try as @VasilyIvoyzha suggested by not using `Cells` at all? And yes, you can still use variable rows. – braX Jan 27 '20 at 00:36
  • 1
    Not only rows, but also columns. Your first suggestion was exactly what I needed. – Martin Panacek Jan 28 '20 at 06:56

1 Answers1

0

Try this code.

Dim INP As Worksheet, TMP As Worksheet

Set INP = Sheets("Input")
Set TMP = Sheets("Temp")
INP.Range(Cells(1, 1), Cells(1, 3)).Copy Destination:=TMP.Cells(1, 1)

This code will paste values and formats.

Variatus
  • 14,293
  • 2
  • 14
  • 30