Solved: Merge Workbooks

[maxflia10]

I've "volunteered" to record golf scores for a tournament. I need to record about 90 scores, hole by hole. In order to speed up the data entries, I'm thinking of using three laptops then merging the scores into a master sheet.

I've named the workbooks, Score1, Score2, and Score3. Ideally, I'd like to merge those scores into Score1.

I initially thought I could use from the Tools menu, Compare & Merge Workbooks. But, those workbooks would have to be on a network and shared.

I suppose I could copy and paste, but was looking for something "easier". Anyone have any ideas?

TIA

[mdmackillop]

Assuming you want to do this "live", you could set up a SaveCopyAs routine on two laptops to save to a USB key, then import that to the Master sheet. Would that suit your requirements? Do you need further assistance?

[maxflia10]

Assuming you want to do this "live", you could set up a SaveCopyAs routine on two laptops to save to a USB key, then import that to the Master sheet. Would that suit your requirements? Do you need further assistance?

Thanks, yes that was part of the plan, it's the merging of the other two WB I need help on. How would I import the sheets? Would I copy and paste?

[mdmackillop]

Can you post a copy of your spreadsheet. We could put together a macro to import if we have the data layout.

[maxflia10]

Can you post a copy of your spreadsheet. We could put together a macro to import if we have the data layout.

Hope I'm doing this correctly........

[mdmackillop]

This is a very simple solution, it assumes the "helper" sheets have all the names as the master. The scores are simply copied to the master as they are completed. I've put code for Save and Import into the attachment. Change the USB path to suit.

If the "helpers" are only to have selected teams, a Find solution would be required; not a problem, so let us know your preference.


'Sheet Code
Private Sub CommandButton1_Click()
Application.Calculation = xlCalculationManual
GetData1 "J:\AAA\sample1.xls"
Application.Calculation = xlCalculationAutomatic
End Sub

Private Sub CommandButton2_Click()
ActiveWorkbook.SaveCopyAs "J:\AAA\sample1.xls"
End Sub


'Module code
Option Explicit

Sub GetData1(Pth As String)
Dim wbSource As Workbook
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim i As Long

Set wsTarget = ActiveSheet
Set wbSource = Workbooks.Open(Pth)
Set wsSource = wbSource.Sheets("Score")

For i = 10 To 90
If wsSource.Cells(i, 4) <> "" Then
wsSource.Cells(i, 4).Resize(, 9).Copy wsTarget.Cells(i, 4)
wsSource.Cells(i, 14).Resize(, 9).Copy wsTarget.Cells(i, 14)
End If
Next
wbSource.Close False
End Sub

[maxflia10]

Thank-you very much!