Assign multiple cell's values to a variable

[mikeburg]

Need exact VBA code syntax to assign a workbook (to be closed) sheet's
cells J4 to J72 values to a variable called "ColJValues" to be assigned
to another sheet (to be opened later in the macro). The values are all
dates.

Once the other workbook is opened later in the macro, need the exact
syntax to assign the value in the above variable, "ColJValues", to it's
cells J4 to J72.

This would greatly help a learner. Thanks, mikeburg

 

[Leith Ross]

Hello Mikeburg,

This macro should illustrate how to copy the range of one workbook to
the range of another workbook. The code to do this must reside in your
main workbook which isn't closed until you exit Excel. Also the code
assumes the required workbooks are already open. Remember this is to
illustrate the process. It isn't a tailored solution written for your
project. If you have any problems, it would be best to post the code
you have written for review.


Code:
--------------------

Sub CopyToWorkbook()

Dim ColJValues As Range
Dim Wkb1 As Workbook
Dim Wkb2 As Workbook
Dim Wkb1Name As String
Dim Wkb2Name As String
Dim Wks1Name As String
Dim Wks2Name As String

'Set the Workbook file names. This assume the Workbooks are already open
Wkb1Name = "My Test Book 1.xls"
Wkb2Name = "My Test Book 2.xls"

'Set the Worksheet names
Wks1Name = "Sheet1" '<<< Change to match your sheet's name in Workbook 1
Wks2Name = "Sheet1" '<<< Change to match your sheet's name in Workbook 2

'Copy values from Workbook 1 into the variable ColJValues
Set Wkb1 = Workbooks(Wkb1Name)
Set ColJValues = Wkb1.Worksheets(Wks1Name).Range("J4:J72")
Addx = ColJValues.Cells(1, 1).Address

'Copy the variable's data over to Workbook 2
Set Wkb2 = Workbooks(Wkb2Name)
ColJValues.Copy Destination:=Wkb2.Worksheets(Wks2Name).Range(Addx)

End Sub

 

[jodhis]

I tried this code...I get an error message on this line:
Addx = ColJValues.Cells(1, 1).Address

'Object variable or With block variable not set'

Jodhi