CODEWITHSUNDEEP
excelvba
Excel For Freelancers
Excel For Freelancers

Reputation: 156

Excel VBA Code to Copy Data based on a cell reference to another sheet

I would like to have data from one sheet copied to another sheet in Excel. The location of the data in the firs sheet is static, while the Sheet, Column, and Row in which the data is being copied is variable, and based on cell data. I would like a bit of help with the VB Code. Consider the following Excel Table Sample Data:

`Sheet1`
'A1 = Apples'
'B1 = Sheet2'
'C1 = 5'
'D1 = 10'

In the above sample, i would like to have a VB code place "Apples" on Sheet2 at Column 5 (can be column E as well), Row 10. On the click of a button, this will be copied over. Thanks so much in advance for your help in this matter. Randy

Upvotes: 0

Views: 9443

Answers (1)

Tim Williams
Tim Williams

Reputation: 166980

Dim sht, rw, col, val

With ThisWorkbook.sheets("Sheet1")
  val = .Range("A1").Value
  sht = .Range("B1").Value
  col = .Range("C1").Value
  rw = .Range("D1").Value
End with

if isnumeric(col) then
   thisworkbook.sheets(sht).Cells(rw,col).value = val
else
  thisworkbook.sheets(sht).Range(col & rw).value = val
end if

Upvotes: 1

Related Questions