Copy data from a different worksheet in Excel

Question

I am new to VBA. I have written this code inside a button click event in sheet called "Outputs". The problem is when I try to copy data form TestInput sheet to Calculator sheet I get a run time error 1004.
My code is shown below

 Do While currentInd < lastRows
   With Worksheets("TestInput")
    Worksheets("Calculator").Range(Cells(3, "C"), Cells(3, (lastColumns))).Value = .Range(.Cells(currentInd, "A"), .Cells(currentInd, lastColumns)).Value

  End With

If I replace 3rd line of my code with Worksheets("Calculator").Range("C3:FC3").Value = .Range(.Cells(currentInd, "A"), .Cells(currentInd, lastColumns)).Value

then Its working fine but "lastColumns" is a variable which would change, so I dont want to fix my range to "C3:FC3"

assylias assylias · Accepted Answer · 2012-03-12T12:06:34

Unless "Calculator" is the current active sheet, you need to fully specify the location of the cells:

With Worksheets("TestInput")
    Worksheets("Calculator").Range(Worksheets("Calculator").Cells(3, "C"), Worksheets("Calculator").Cells(3, lastColumns)).Value = .Range(.Cells(currentInd, "A"), .Cells(currentInd, lastColumns)).Value
End With

Or to make it a little more readable:

Dim shCalc as WorkSheet
Set shCalc = Worksheets("Calculator")
With Worksheets("TestInput")
    shCalc.Range(shCalc.Cells(3, "C"), shCalc.Cells(3, lastColumns)).Value = .Range(.Cells(currentInd, "A"), .Cells(currentInd, lastColumns)).Value
End With

Copy data from a different worksheet in Excel

1 Answers