Assign cell value to variable
 

I know this is very basic but I have two workbooks open and I declare (DIM)
and assign all workbook names and worksheet names. I simply want to take
values from specfic cells in workbookA.WorksheetA.Cell A1 and assign it to a
variable and then goto workbookB.etc. and put the value in cell A1.

How can I do this efficiently and then also how can I do this using variables?

Thanks....

Assign cell value to variable
 

Dim shA as Worksheet
Dim shB as Worksheet
set shA = WorkBooks("WorkbookA.xls").Worksheets("WorksheetA" )
set shB = WorkBooks("WorkbookB.xls").Worksheets("WorksheetB" )

for i = 1 to 10
for j =1 1 to 5
shA.Cells(i,j).Value = shB.Cells(i,j).Value
Next j
Next i

or
shA.Range("A1:F10").Value = shB.Range("A1:F10").Value

to use a variable

for i = 1 to 10
for j =1 1 to 5
v = shB.Cells(i,j).Value
shA.Cells(i,j).Value = v
Next j
Next i

--
Regards,
Tom Ogilvy




"Rookie_User" wrote:

I know this is very basic but I have two workbooks open and I declare (DIM)
and assign all workbook names and worksheet names. I simply want to take
values from specfic cells in workbookA.WorksheetA.Cell A1 and assign it to a
variable and then goto workbookB.etc. and put the value in cell A1.

How can I do this efficiently and then also how can I do this using variables?

Thanks....

Assign cell value to variable
 

The book where the code is running is always Thisworkbook so there really is
no need to declare an object for it. For this example I will do it anyway...

Dim wbkA As Workbook
Dim wbkB As Workbook
Dim wksA1 As Worksheet
Dim wksB1 As Worksheet
Dim var As Variant

Set wbkA = ThisWorkbook
On Error Resume Next
Set wbkB = Workbooks("BookB.xls")
On Error GoTo 0
If wbkB Is Nothing Then Set wbkB = Workbooks.Open("C:\BookB.xls")

Set wksA1 = wbkA.Sheets(1)
Set wksB1 = wbkB.Sheets(1)

'make BookB Sheet(1) cell A1 = BookA Sheet(1) cell A1
wksB1.Range("A1").Value = wksA1.Range("A1").Value
'make BookB Sheet(1) cell B1 = BookA Sheet(1) cell B1 with a variable
var = wksA1.Range("B1").Value
wksB1.Range("B1").Value = var

--
HTH...

Jim Thomlinson


"Rookie_User" wrote:

I know this is very basic but I have two workbooks open and I declare (DIM)
and assign all workbook names and worksheet names. I simply want to take
values from specfic cells in workbookA.WorksheetA.Cell A1 and assign it to a
variable and then goto workbookB.etc. and put the value in cell A1.

How can I do this efficiently and then also how can I do this using variables?

Thanks....