CODEWITHSUNDEEP
excelvba
Josh
Josh

Reputation: 1

Read cell and use text as input for VBA formula

I've been searching for a long time now to find a simple solution for my problem but I just can't find it. My problem is that I want to reference a cell and use it as text in my VBA code.

eg: say the cell = C104

VBA would do:

wb2.Sheets("Cell").Range("F8:F22").Copy
wb1.Sheets("asd").Range("B7:B21").PasteSpecial

This doesn't work because it isn't actually using the text from the cell. I've tried using cell().Text and .value but they don't work.

Upvotes: -1

Views: 16238

Answers (1)

user4039065
user4039065

Reputation:

You have multiple workbooks and multiple worksheets available that could potentially be the parent of cell. You need to reference the cell definitively and take its .Value or .Text to be used as a worksheet name without relying on Activesheet for a default parent.

dim myWS as string
myWS = wb1.Sheets("asd").Range("C104").value  '◄ this gets the value of cell. I don't know if I have referenced the correct workbook and worksheet
wb2.Sheets(myWS).Range("F8:F22").Copy _
  destination:=wb1.Sheets("asd").Range("B7")

Upvotes: 2

Related Questions