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.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[[email protected]]

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



Leith Ross wrote:
> 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
>
> --------------------
>
>
> Sincerely,
> Leith Ross
>
>
> --
> Leith Ross
> ------------------------------------------------------------------------
> Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
> View this thread: http://www.excelforum.com/showthread...hreadid=564020

[[email protected]]

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



Leith Ross wrote:
> 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
>
> --------------------
>
>
> Sincerely,
> Leith Ross
>
>
> --
> Leith Ross
> ------------------------------------------------------------------------
> Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
> View this thread: http://www.excelforum.com/showthread...hreadid=564020