Variable Name in a Range Table Object?

[Coeus]

Hi all,

I'm a bit stuck on this so any help would be very much appreciated!

Basically I'm trying to do two thing: (1) Create a name of a table using a string variable (this works okay) and (2) Copy and paste some data from a column into another column which has the same name generated in (1).

For example:
- Cell A1 = July.
- period_name = July Month
- Copy from a column (in this case called period data) within a table object.
- Paste into the same table with the column name as defined by my variable (i.e. July Month).


This is my macro as it stands. I have bolded where the variable is included in the range name of the table object as I'm pretty sure this is where it is going wrong


Dim period_name As String

period_name = Sheets("Sheet1").Range("A1") & " Month"

Sheets("ZZZ Data").Range("ZZZ_Data[Period Data]").Copy

Sheets("ZZZ Data").Range("ZZZ_Data[period_name]").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub


Many thanks,

Coeus.

[TMS]

Try:

Please Login or Register  to view this content.

Regards, TMS

[shg]

Please Login or Register  to view this content.

period_name can't have a space in it.

Please take a few minutes to read the forum rules and edit your post to add CODE tags.