2

I'm trying to put together a macro which will select certain columns and paste them into a new sheet. The problem is that columns tend to be added and deleted as people see fit which throws off the absolute referencing.

I have been trying to tinker with the basic macro produced using the macro recorder but I haven't had any luck selecting columns based on their contents. I have a sheet that is generated from our database daily with changing fields. For example, I would like to select just the Part #, Cost, and Contact fields but the addition of the IDN today threw my old macro off.

So far, I've tried to use basic excel find functions like vlookup, index against a list of constants and the find function below but none seem to work. Is there something I am missing here to select the column with my desired text?

Columns(find("Part #")).Select

enter image description here

CallumDA
  • 12,025
  • 6
  • 30
  • 52
enmasse
  • 143
  • 2
  • 13

2 Answers2

1

You need to specifically find the column, then you can paste off to a destination location, such as: 

Sheets("Source").Columns(Sheets("Source").Rows(1).Find("Part #").Column).Copy Sheets("Dest").Cells(1,1)
Cyril
  • 6,448
  • 1
  • 18
  • 31
  • Replacing both sources with the "Sheet1" throws an runtime error 9, out of range error. I've tried activating the sheet in case it wasn't in focus, but the same error is given. – enmasse Mar 05 '18 at 20:49
  • @enmasse verify that you are indeed searching in Rows(1) for "Part #". Having just retested this, I'm not getting an error with that provisional (row to search = 1). Also verify that you're pasting to Sheets("Dest").Cells(1,1) – Cyril Mar 05 '18 at 20:56
1

Firstly, be careful with .Select and it looks like you're not fully qualifying your references to the worksheet so take note of the full Workbook.Worksheet.Range type referencing below.

Here's your quick fix:

Public Sub Test()
    'the Range approach
    ThisWorkbook.Worksheets("Sheet1").Rows(1).Find(What:="Part #", LookAt:=xlWhole).EntireColumn.Copy
    ThisWorkbook.Worksheets("Sheet2").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats 'or xlPasteAll to include formulas
End Sub

Using entire column ranges isn't usually the best idea. It will work, but only as long as you remember to paste your copied column into the first row of the destination worksheet every time.

Here's the better option (IMO):
Convert your data to a Table (know as a ListObject in VBA) by clicking "format as table" on the home tab of the ribbon. It's now much easier to reference your column - just do it by name, no need to use Find.

Public Sub Test()
    'the ListObject approach
    ThisWorkbook.Worksheets("Sheet1").ListObjects("MyTable").ListColumns("Part #").Copy
    ThisWorkbook.Worksheets("Sheet2").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats 'or xlPasteAll to include formulas
End Sub

You need to know the name of your table (I called it MyTable here), which you can do by clicking on "Table" on the ribbon when your table is selected

CallumDA
  • 12,025
  • 6
  • 30
  • 52