Excel Question, Hoping someone can give me some advice to save me some time.

[redbull]

I am wanting to make a macro in excel, I have around 40 cells that need to be copied and pasted into other cells across 9 worksheets. Is there a way to dim all of these cells once so I can use them later? I will have to do individual cells, and worksheets.

An example of what I am trying to do, keep getting error 1004

Sub Test()
Dim VName As String
VName = Range("B:21").Value
End Sub

[JoeM]

VName = Range("B:21").Value

You have a syntax error there. If you want the value from cell B21, it should look like:
VName = Range("B21").Value

[redbull]

Wow, thank you.
So now that I have everything setup, how do I paste into another cell on another sheet?

[JoeM]

A few ways to go about that.

You could do it like this:

Sheets("Sheet2").Range("A1") = VName

Or you could even bypass the need for a "VName" variable altogether, and use something like this:

Sheets("Sheet2").Range("A1") = Sheets("Sheet1").Range("B21")

[ipisors]

You should declare workbook, worksheet and range variables or at least worksheet. Never use Excel vba without explicitly referring to objects.

Example:

dim ws1 as Worksheet
dim ws2 as Worksheet
set ws1=ThisWorkbook.Worksheets("Name of Worksheet")
set ws2=ThisWorkbook.Worksheets("Name of Another Worksheet")
ws2.Range("B21").Value = ws1.Range("B21").Value '(or whatever you want to do)