Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assign multiple cell's values to a variable
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 -- mikeburg ------------------------------------------------------------------------ mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581 View this thread: http://www.excelforum.com/showthread...hreadid=564020 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assign multiple cell's values to a variable
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assign multiple cell's values to a variable
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
retrieving multiple corresponding values with variable rows/column | Excel Worksheet Functions | |||
how to sum all values of a variable that's repeated multiple times | Excel Worksheet Functions | |||
Lookup Multiple Values from a variable table size | Excel Worksheet Functions | |||
Assign value to a variable name | Excel Discussion (Misc queries) | |||
Assign value to variable | Excel Discussion (Misc queries) |