CODEWITHSUNDEEP
vbaexcel
Aaron C
Aaron C

Reputation: 135

VBA select column that matches value and copy data to another workbook

I have two excel workbooks, rh and summary. I have code in the summary that checks the rh file to find a specific column based on the text within it. I need to find these columns, copy them and send the data to the summary sheet.

Sub Main()

' Declare hr workbook file
Dim rh As Excel.Workbook

' initialise hr workbook file
Set rh = Workbooks.Open("C:\Users\AC74338\Desktop\ac\rh")

'select the rh file
rh.Select

'select the open worksheet
Sheets("Open").Select

' search for the column header that matches e.g. "Org Level 6"
OrgLevel6 = WorksheetFunction.Match("Org Level 6", Rows("1:1"), 0)
OrgLevel7 = WorksheetFunction.Match("Org Level 7", Rows("1:1"), 0)

'activate this workbook where the code is
ThisWorkbook.Activate

'paste the data that was copied to this workbook in the NxNOpen worksheet
ThisWorkbook.Sheets("NxNOpen").Columns(OrgLevel6).Copy Destination:=Sheets("Sheet2").Range("A1")
ThisWorkbook.Sheets("NxNOpen").Columns(OrgLevel7).Copy Destination:=Sheets("Sheet2").Range("B1")

End Sub

I have debugged and get an error when assigning the data to the OrgLevel6 variable. Anyone have any suggestions on how to fix this? not very knowledgeable with VB so may be a simple mistake.

Upvotes: 2

Views: 1157

Answers (4)

lonestorm
lonestorm

Reputation: 380

Change Rows("1:1") to Rows(1).

You are applying the Match function on the worksheet named "Open", but you are copying the data from sheet "NxNOpen". Right column, wrong sheet

Upvotes: 0

Mikku
Mikku

Reputation: 6664

Please try the code below: 

Sub Main()

' Declare hr workbook file
Dim rh As Excel.Workbook
Dim sum As Excel.Workbook

Dim b As Integer

' initialise hr workbook file
Set rh = Workbooks.Open("C:\Users\AC74338\Desktop\ac\rh")
Set sum = ThisWorkbook


b = 1

lcol = rh.Sheets("Open").Cells(1, Columns.Count).End(xlToLeft).Column

For i = 1 To lcol

If rh.Sheets("Open").Cells(1, i).Value = "Org Level 6" Or "Org Level 7" Then

rh.Sheets("Open").Columns(i).Copy sum.Sheets("Sheet2").Cells(1, b)
b = b + 1

End If

Next i

End Sub

Upvotes: 0

Carrosive
Carrosive

Reputation: 899

I have tested your OrgLevel6 = line and it works fine with one sheet, so it's possible that it's executing without the correct sheet active and as a result is unable to find "Org Level 6".

Try changing Sheets("Open").Select to Sheets("Open").Activate

Upvotes: 0

Jordan
Jordan

Reputation: 4514

When you're using a WorksheetFunction in VBA you need to still use VBA Range references so instead of using Rows(1:1) you'll need to refer to the row in VBA code. For example:

OrgLevel6 = WorksheetFunction.Match("Org Level 6", rh.Sheets("Open").Rows(1), 0)
OrgLevel7 = WorksheetFunction.Match("Org Level 7", rh.Sheets("Open").Rows(1), 0)

Upvotes: 3

Related Questions