get data from another sheet by specific column header

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Stephen King - 19 Aug 2004 14:51 GMT

I would like to be able to get data from a different workbook and have it
inserted into a current worksheet.

get all data under column header DATE in ocnewdata.xls and put in column A
get all data in column VERIFIER in ocnewdata.xls and put in column B etc.

I'm trying to do this because the originating spread sheet can have varying
columns in number and name and position but I only want the certain columns
of data.
I tried getdata but I only get the whole worksheet.

any suggestions would be appreciated
TIA

Signature

Stephen King
ACC Brisbane Australia

Reply to this Message

Bill Manville - 19 Aug 2004 15:39 GMT

> I would like to be able to get data from a different workbook and have it
> inserted into a current worksheet.
>
> get all data under column header DATE in ocnewdata.xls and put in column A
> get all data in column VERIFIER in ocnewdata.xls and put in column B etc.

There are a number of options available:

1) Data / Import External Data / New Database Query
2) Data / Consolidate
3) Data / Filter/ Advanced Filter / Copy to another location

Depends on whether you are able to have the source workbook open at the
time, and whether you are happy to use a macro to do the update.

If the answer to both is yes, I would use (3) and a macro in the destination
workbook along the lines of

Sub GetMyData()
With ThisWorkbook.Sheets("Destination")
'clear old content but leave headings
.Activate
.Range("A1").CurrentRegion.Offset(1).ClearContents
Workbooks("SourceBook.xls").Sheets("SourceSheet"). _
ActiveWorkbook.Range("A1").CurrentRegion.AdvancedFilter _
action:=xlFilterCopy, _
CopyToRange:=.Range("A1").CurrentRegion
End With
End Sub

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Reply to this Message