0

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

Excel For Freelancers
  • 156
  • 2
  • 6
  • 17
  • 1
    Worksheets("Sheet2").Range("E10") = Worksheets("Sheet1").Range("A1") Something like that? – Lil'Monkey Oct 25 '11 at 14:32
  • 1
    possible duplicate of [Copy data from another Workbook through VBA](http://stackoverflow.com/questions/7401967/copy-data-from-another-workbook-through-vba) - you could have made a search on Stackoverflow though – JMax Oct 25 '11 at 15:05
  • @Lil'Monkey: Thank u for the suggestion however the Sheet2 E10 values are relative, and can/ will change, so i was hoping for some code that would copy the values from Sheet 1 so, i was thinking something like Worksheets.(B1).Range(C1&D1)= Worksheets("Sheet1").Range("A1") Of course that formula would never work, but something like that might. – Excel For Freelancers Oct 25 '11 at 16:28
  • @JMax: I did do a pretty extensive search. The sample you gave me, I believe, simply copies data from one workboook to another. Since the location that the data being copied is variable, i think it might be more complex? – Excel For Freelancers Oct 25 '11 at 16:30
  • just wondering why Worksheets(B1).Range(C1&D1)= Worksheets("Sheet1").Range("A1") woulnd work? i know your the one asking a question lol but i never really got into excel vba i mean your variables are valid strings why cant u just plug them as parameters – Lil'Monkey Oct 25 '11 at 17:33
  • Actually.. hmm i did not even try it.. i am just guessing it would not since it was based purely on a guess. and i dont guess that well. I am a newbie at this :) – Excel For Freelancers Oct 25 '11 at 18:28

1 Answers1

1
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
Tim Williams
  • 154,628
  • 8
  • 97
  • 125