• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • Last Modified:

Copy specific data columns from masterdata to file based on combo box selection

I have attached 2 files
'standardinfo.xls' which contains Job Titles in column A and Regional rates (London column B, etc) which is updated centrally.
'info.xlm' which has a macro (working fine) to copy data from 'standardinfo'
I now need to amend the macro to only draw the column of data selected in combobox in cell B2 so that column A still has titles and column B has the selected regional rates.

Points awarded on the basis of value to me, not perceived complication
Info.xlsm
standardinfo.xls
0
grayderek
Asked:
grayderek
  • 2
1 Solution
 
SteveCommented:
This code should do the job:
Sub GetTW_Data()

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim inList As Long

Set wb1 = ThisWorkbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = wb1.Sheets(1)

inList = Application.WorksheetFunction.Match(ws1.Range("B2"), ws1.Range("B5:I5"), 0)

ws1.Range(ws1.Cells(6, inList), ws1.Cells(70, inList)).ClearContents

Set wb2 = Workbooks.Open(Filename:="C:\standardinfo.xls", ReadOnly:=True)

Set ws2 = wb2.Sheets(1)
ws2.Range(ws2.Cells(2, inList), ws2.Cells(66, inList)).Copy 'Copy data to clipboard
 
ThisWorkbook.Activate 'Return to THIS workbook
ws1.Cells(6, inList).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 [a1].Select 'cancels highlighted paste region
 
Application.CutCopyMode = False
wb2.Close 'close source data workbook

End Sub

Open in new window

0
 
SteveCommented:
First answer refreshes the columns leaving them where they are.
This code below will keep all in one column (B) and change depending on the value selected:
Sub GetTW_Data()

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim inList As Long

Set wb1 = ThisWorkbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = wb1.Sheets(1)



ws1.Range(ws1.Cells(5, 2), ws1.Cells(70, 2)).ClearContents

Set wb2 = Workbooks.Open(Filename:="C:\standardinfo.xls", ReadOnly:=True)

Set ws2 = wb2.Sheets(1)

inList = Application.WorksheetFunction.Match(ws1.Range("B2"), ws2.Range("A1:I1"), 0)
ws2.Range(ws2.Cells(1, inList), ws2.Cells(66, inList)).Copy 'Copy data to clipboard
 
ThisWorkbook.Activate 'Return to THIS workbook
ws1.Cells(5, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 [a1].Select 'cancels highlighted paste region
 
Application.CutCopyMode = False
wb2.Close 'close source data workbook

End Sub

Open in new window

Info.xlsm
0
 
grayderekAuthor Commented:
Absolutely perfect exactly as I asked
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now