macro for copy-pasting from different workbooks

[Binty]

Hi all,

I have to do the following manually everyday to do my daily report.

Copy data from certain columns in a set of data and paste it into a
different workbook, e.g. I want to select only those column with heading 'SR
Num' and 'Date'. However, 'SR Num' will not always be in a particular column
- it can be in 'A', 'B', wherever.

How do I write a macro where it will

- find the columns with headings I want to select,
- copy all the data (rows of data vary day to day) and
- paste it into a different workbook (location for pasting is constant,
however, the file where I'm getting the data from changes everyday)?

Thanks!

Binty

 

[Bernie Deitrick]

With the active workbook being the file that you want to get the data from, you could use something
like

Intersect(Cells.Find("SR Num").EntireColumn, ActiveSheet.UsedRange).Copy _
ThisWorkbook.Worksheets("Paste Sheet").Range("A2")

Intersect(Cells.Find("Date").EntireColumn, ActiveSheet.UsedRange).Copy _
ThisWorkbook.Worksheets("Paste Sheet").Range("B2")

HTH,
Bernie
MS Excel MVP