Copy directly to variable

[KGee]

Is it possible to use the copy method and assign the results directly to a variable which can be used to later in a macro?

I want to copy several cells and ranges from various sheets in one workbook with the results of each copy being assigned to a different variable. Then later in the code I want call those variables and paste the contents into various sheets in a different workbook.

 

[ColinKJ]

Hi KGee,

You could try this:

Code:

Sub test()
With Workbooks("Book3").Sheets(1)
For a = 1 To 3
ThisWorkbook.Sheets(1).Cells(4, a) = .Cells(4, a)
Next a
End With
End Sub

Code:

If you don't want to copy the values directly, you could first put them into a vairable, then put them into the desired WB.

ColinKJ

 

[KGee]

Hi Colin,

Can you elaborate on how I could use your code for my needs? I'm not sure how I would go about assigning the specific cells I want to my variables.

What I'm looking to do is something like so:
open "Book1" and assign all the variables I need at one time
copy cell C5 from Sheet1 to var1
copy range B6:B8 from Sheet2 var2
copy cell D2 from Sheet3 to var3
etc..

then open "Book2" and place them accordingly
paste var1 in cell B2 of Sheet2
paste var2 in range E6:E8 of Sheet3
paste var3 in cell F4 of Sheet1

Thanks - Kevin

 

[ColinKJ]

Hi KGee,

The following assumes both workbooks are open, and the code is in Workbook 1.

Code:

Sub CopyData()
With Workbooks("Book 2")
.Sheets(2).Cells(2, 2) = ThisWorkbook.Sheets(1).Cells(5, 3)
For a = 6 To 8
.Sheets(3).Cells(a, 5) = ThisWorkbook.Sheets(2).Cells(a, 2)
Next a
.Sheets(1).Cells(4, 5) = ThisWorkbook.Sheets(3).Cells(2, 4)
'Etc
End With
End Sub

Code:

ColinKJ

 

[mikerickson]

This will set myArray to a 2D array filled with the values in the first range.
It then puts those values in the second range.

Dim myArray as Variant

myArray = Workbooks("Book1").Sheets("sheet1").Range("A1:B3").Value

Workbooks("BookTwo").Sheets("sheet2").Range("C11:D13").Value = myArray

 

[KGee]

Guys,

I will try these out tomorrow when I have time. Thanks for the help.

Kevin