SOLVED

Copy specific data from one workbook to another

Copper Contributor
Hi,
Can anyone kindly offer some advice on the following?......
I have two reports with different data, one for members and one for those who have used our services (both have the name column).
What I need is for the services report to show me all the data it currently lists plus which clients are members and what type of membership they have (which is on the membership report).
Unfortunately our database is not clever enough to run this search and pull off the data in one, so I wonder if i can do this within excel? Vlookup?
Many thanks
K
7 Replies

@Kerrie1383 

It is hard to suggest any solution without looking at the layout of your data in the reports.

Why not upload the sample file and mock up the desired output in another sheet manually to show us the end result you are trying to achieve?

@Subodh_Tiwari_sktneer

 

Thanks, please see below.

The membership report & service report are both in separate workbooks.

Sample reports.jpg

@Kerrie1383 

Instead of posting the image of the data, please upload the Excel Workbooks.

Click on the paper clip icon to open the upload window and upload the files. Refer to the following image.

Upload File.jpg

 

Btw are you comfortable with the Macro Solution? And what Excel version you are using, Excel 2010?

@Subodh_Tiwari_sktneer 

 

Thanks I have attached the sample files as requested.

 

I am have some basic knowledge of Macro but i have never used this. I am currently using excel 2010.

 

Many thanks

 

Kerrie

best response confirmed by Kerrie1383 (Copper Contributor)
Solution

@Kerrie1383 

Please find the attached with the code on Module1 and a button called "Generate Combined Membership Report" on Combined Report Sheet to run the code.

 

The code will open the file picker dialog box for you to pick the Membership Report first and then Service Report, once you pick both the files, the code will generate the combined report on the Combined Report Sheet.

 

Code on Module1:

Public Sub ExtractMembershipInfo()
Dim wbCombined      As Workbook
Dim wbMembership    As Workbook
Dim wbService       As Workbook
Dim wsCombined      As Worksheet
Dim wsMember        As Worksheet
Dim wsService       As Worksheet
Dim strFilePath     As String
Dim x               As Variant
Dim y()             As Variant
Dim dict            As Object
Dim i               As Long

Application.ScreenUpdating = False

Set wbCombined = ThisWorkbook
Set wsCombined = wbCombined.Worksheets("Combined Report")

With Application.FileDialog(msoFileDialogFilePicker)
    .Title = "Select Membership Report File!"
    .AllowMultiSelect = False
    .ButtonName = "Open Membership Report"
    .Filters.Clear
    .Filters.Add "Excel Files", "*.xlsx"
    If .Show = -1 Then
        strFilePath = .SelectedItems(1)
    Else
        MsgBox "You didn't select any Membership Report.", vbExclamation
        End
    End If
End With

Set wbMembership = Workbooks.Open(strFilePath)
Set wsMember = wbMembership.Worksheets(1)

With Application.FileDialog(msoFileDialogFilePicker)
    .Title = "Select Service Report File!"
    .AllowMultiSelect = False
    .ButtonName = "Open Service Report"
    .Filters.Clear
    .Filters.Add "Excel Files", "*.xlsx"
    If .Show = -1 Then
        strFilePath = .SelectedItems(1)
    Else
        MsgBox "You didn't select any Service Report.", vbExclamation
        End
    End If
End With

Set wbService = Workbooks.Open(strFilePath)
Set wsService = wbService.Worksheets(1)

Set dict = CreateObject("Scripting.Dictionary")

x = wsMember.Range("A1").CurrentRegion.Value

For i = 2 To UBound(x, 1)
    dict.Item(x(i, 1)) = x(i, 2) & "_" & x(i, 3)
Next i

wbMembership.Close False

wsCombined.Cells.Clear

wsService.Range("A1").CurrentRegion.Copy wsCombined.Range("A1")

wbService.Close False

x = wsCombined.Range("A1").CurrentRegion.Value
ReDim y(1 To UBound(x, 1), 1 To 2)

wsCombined.Cells(1, UBound(x, 2) + 1) = "Membership Type"
wsCombined.Cells(1, UBound(x, 2) + 2) = "Membership Status"

For i = 2 To UBound(x, 1)
    If dict.exists(x(i, 1)) Then
        y(i - 1, 1) = Split(dict(x(i, 1)), "_")(0)
        y(i - 1, 2) = Split(dict.Item(x(i, 1)), "_")(1)
    End If
Next i

wsCombined.Cells(2, UBound(x, 2) + 1).Resize(UBound(x, 1), 2).Value = y

With wsCombined.Range("A1").CurrentRegion
    .Rows(1).Font.Bold = True
    .Rows(1).Font.Size = 13
    .Columns.AutoFit
    .Borders.Color = vbBlack
End With

Application.ScreenUpdating = True
End Sub

 

 

@Subodh_Tiwari_sktneer 

 

OMG this is amazing, it worked on the sample sheets but not on the original reports, I assume this is because the samples did not include all data as per the original report.

 

I did however match the fields and it seemed worked... so I  have just sort the list ascending by name and copy and paste on to the original report.

 

Thank you so much for your help, I really appreciate it.

 

Kindest regards

 

K

@Kerrie1383 

You're welcome! Glad it worked as desired and you were able to tweak it as per your need.

 

Please take a minute to select the post with the proposed solution as a Best Response/Answer to mark your question as Solved.

1 best response

Accepted Solutions
best response confirmed by Kerrie1383 (Copper Contributor)
Solution

@Kerrie1383 

Please find the attached with the code on Module1 and a button called "Generate Combined Membership Report" on Combined Report Sheet to run the code.

 

The code will open the file picker dialog box for you to pick the Membership Report first and then Service Report, once you pick both the files, the code will generate the combined report on the Combined Report Sheet.

 

Code on Module1:

Public Sub ExtractMembershipInfo()
Dim wbCombined      As Workbook
Dim wbMembership    As Workbook
Dim wbService       As Workbook
Dim wsCombined      As Worksheet
Dim wsMember        As Worksheet
Dim wsService       As Worksheet
Dim strFilePath     As String
Dim x               As Variant
Dim y()             As Variant
Dim dict            As Object
Dim i               As Long

Application.ScreenUpdating = False

Set wbCombined = ThisWorkbook
Set wsCombined = wbCombined.Worksheets("Combined Report")

With Application.FileDialog(msoFileDialogFilePicker)
    .Title = "Select Membership Report File!"
    .AllowMultiSelect = False
    .ButtonName = "Open Membership Report"
    .Filters.Clear
    .Filters.Add "Excel Files", "*.xlsx"
    If .Show = -1 Then
        strFilePath = .SelectedItems(1)
    Else
        MsgBox "You didn't select any Membership Report.", vbExclamation
        End
    End If
End With

Set wbMembership = Workbooks.Open(strFilePath)
Set wsMember = wbMembership.Worksheets(1)

With Application.FileDialog(msoFileDialogFilePicker)
    .Title = "Select Service Report File!"
    .AllowMultiSelect = False
    .ButtonName = "Open Service Report"
    .Filters.Clear
    .Filters.Add "Excel Files", "*.xlsx"
    If .Show = -1 Then
        strFilePath = .SelectedItems(1)
    Else
        MsgBox "You didn't select any Service Report.", vbExclamation
        End
    End If
End With

Set wbService = Workbooks.Open(strFilePath)
Set wsService = wbService.Worksheets(1)

Set dict = CreateObject("Scripting.Dictionary")

x = wsMember.Range("A1").CurrentRegion.Value

For i = 2 To UBound(x, 1)
    dict.Item(x(i, 1)) = x(i, 2) & "_" & x(i, 3)
Next i

wbMembership.Close False

wsCombined.Cells.Clear

wsService.Range("A1").CurrentRegion.Copy wsCombined.Range("A1")

wbService.Close False

x = wsCombined.Range("A1").CurrentRegion.Value
ReDim y(1 To UBound(x, 1), 1 To 2)

wsCombined.Cells(1, UBound(x, 2) + 1) = "Membership Type"
wsCombined.Cells(1, UBound(x, 2) + 2) = "Membership Status"

For i = 2 To UBound(x, 1)
    If dict.exists(x(i, 1)) Then
        y(i - 1, 1) = Split(dict(x(i, 1)), "_")(0)
        y(i - 1, 2) = Split(dict.Item(x(i, 1)), "_")(1)
    End If
Next i

wsCombined.Cells(2, UBound(x, 2) + 1).Resize(UBound(x, 1), 2).Value = y

With wsCombined.Range("A1").CurrentRegion
    .Rows(1).Font.Bold = True
    .Rows(1).Font.Size = 13
    .Columns.AutoFit
    .Borders.Color = vbBlack
End With

Application.ScreenUpdating = True
End Sub

 

 

View solution in original post