How to Copy a Specific Field (Column) from a Named Table

BrianExcel

Well-known Member
Joined
Apr 21, 2010
Messages
975
I have a table named "FinalRange90DayBallEdits". The second column of that rate is the date, with each row being a specific calendar day.

When I copy the whole table, in VBA it shows the table selected as

VBA Code:
SourceData:="Sum90DayBallEditsTable[#All]"

Is there a way to use the same code to specifically copy the "Date" column? SOmething like:

With FinalRange90DayBallEdits
Sum90DayBallEditsTable[#Date].copy <INSERT SPECIFIC DESTINATION HERE>


Anyone know how to do this? I would like to do it via this method because I have to create several specific tables with different data, and I would prefer to select them by their specific name versus referencing a specific cell like range(cells(1, 3).

Thanks!
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Try...

VBA Code:
Range("FinalRange90DayBallEdits[Date]").Copy

Hope this helps!
 
Upvote 0
It looks like the copy is working, but I am having problems with writing the right code for the paste. I tried both of the following:

VBA Code:
Range("FinalRange90DayBallEdits[Date]").copy Range("M")

Range("FinalRange90DayBallEdits[Date]").copy Range("M1")

Range("FinalRange90DayBallEdits[Date]").copy cells(1, 13)

Each one of these gave me a "method 'range' of object_Global failed.

Thoughts?

Thank you!
 
Upvote 0
Actually, I think I may have a better way I need some help with....

instead of copying the entire range (since I need to copy offset values for each day as well), how would I reference that range to loop through and identify each value?

So something like:

VBA Code:
With ActiveWorkbook.Sheets("90DayBallEdits")
    For Each r In Range("FinalRange90DayBallEdits[date]")
        msgbox r
    Next r
End With

This way, in addition to referencing the date I can easily reference the offset values as well...
 
Upvote 0
If I understood you correctly, it looks like you simply need to use the Offset property. For example, to refer to the cell to right of the date column, you would do the following...

VBA Code:
r.Offset(, 1).Value

Or, to copy...

VBA Code:
r.Offset(, 1).Copy

Or did I misunderstand?
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top