Merging Excel data from identical copies of a workbook
 

I have two identical workbooks which must be maintained seperately and merged
occasionaly into a third identical MASTER.xls copy. There is no calculation
involved.

What I need to do is to look at each cell in a range and transfer its data
to the MASTER copy - ONLY if there is nothing in that cell already. If data
exists already in that cell of the the MASTER, I want to do nothing and
progress to the next cell.

I will need to do the same thing with at least one other copy.

The data amounts to a 1 used as a tick mark indicating a procedure performed.
MASTER.xls will ultimately have summaries and charts - that part is easy.

I am an OLD COBOL programmer, but a total newbie when it comes to VBA.
Any assistance would be greatly appreciated.
--
Old time COBOL programmer, new VBA programmer.

Merging Excel data from identical copies of a workbook
 

John,

The following code may be something like what you need....
'-------------------------------------------------------------
Sub TransferToMasterSheet()
Dim rngMaster As Excel.Range
Dim rngCell As Excel.Range
Dim shtOne As Excel.Worksheet

On Error Resume Next
'Get all the blank cells in the Master Workbook sheet.
'The Master workbook sheet must be the active sheet.
Set rngMaster = ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlank s)

'Specify the workbook and worksheet that the data is taken from.
'The other workbook must be open.
'You must insert the actual names in the line below...
Set shtOne = Workbooks("WorkbookName").Worksheets("SheetName")
On Error GoTo 0

'Go thru each blank cell in the Master sheet and extract data
'from the corresponding cell in the sheet with data.
If Not rngMaster Is Nothing Then
For Each rngCell In rngMaster
rngCell.Value = shtOne.Range(rngCell.Address).Value
Next
End If

'Clean up
Set rngCell = Nothing
Set rngMaster = Nothing
Set shtOne = Nothing
End Sub
'-----------------------------------------------------------------

Regards,
Jim Cone
San Francisco, USA


"John Guzz" wrote in message
...
I have two identical workbooks which must be maintained seperately and merged
occasionaly into a third identical MASTER.xls copy. There is no calculation
involved.

What I need to do is to look at each cell in a range and transfer its data
to the MASTER copy - ONLY if there is nothing in that cell already. If data
exists already in that cell of the the MASTER, I want to do nothing and
progress to the next cell.

I will need to do the same thing with at least one other copy.

The data amounts to a 1 used as a tick mark indicating a procedure performed.
MASTER.xls will ultimately have summaries and charts - that part is easy.

I am an OLD COBOL programmer, but a total newbie when it comes to VBA.
Any assistance would be greatly appreciated.
--
Old time COBOL programmer, new VBA programmer.

Merging Excel data from identical copies of a workbook
 

Jim,
Thanks for the quick reply.
I think that I failed to fully explain my issue:
The Master will be active because I plan to embed the code in it (probably
in a userform) and I also need to automate it so that someone else can simply
execute it.
I also failed to indicate that the workbooks have multiple sheets and that I
need to loop through all of them.

I also need to limit my search to a specific array of cells (excluding such
things as titles).
Your code may already address those issues. If so, I apologize for my lack
of knowledge. Remember "NEWBIE"

John

"Jim Cone" wrote:

John,

The following code may be something like what you need....
'-------------------------------------------------------------
Sub TransferToMasterSheet()
Dim rngMaster As Excel.Range
Dim rngCell As Excel.Range
Dim shtOne As Excel.Worksheet

On Error Resume Next
'Get all the blank cells in the Master Workbook sheet.
'The Master workbook sheet must be the active sheet.
Set rngMaster = ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlank s)

'Specify the workbook and worksheet that the data is taken from.
'The other workbook must be open.
'You must insert the actual names in the line below...
Set shtOne = Workbooks("WorkbookName").Worksheets("SheetName")
On Error GoTo 0

'Go thru each blank cell in the Master sheet and extract data
'from the corresponding cell in the sheet with data.
If Not rngMaster Is Nothing Then
For Each rngCell In rngMaster
rngCell.Value = shtOne.Range(rngCell.Address).Value
Next
End If

'Clean up
Set rngCell = Nothing
Set rngMaster = Nothing
Set shtOne = Nothing
End Sub
'-----------------------------------------------------------------

Regards,
Jim Cone
San Francisco, USA


"John Guzz" wrote in message
...
I have two identical workbooks which must be maintained seperately and merged
occasionaly into a third identical MASTER.xls copy. There is no calculation
involved.

What I need to do is to look at each cell in a range and transfer its data
to the MASTER copy - ONLY if there is nothing in that cell already. If data
exists already in that cell of the the MASTER, I want to do nothing and
progress to the next cell.

I will need to do the same thing with at least one other copy.

The data amounts to a 1 used as a tick mark indicating a procedure performed.
MASTER.xls will ultimately have summaries and charts - that part is easy.

I am an OLD COBOL programmer, but a total newbie when it comes to VBA.
Any assistance would be greatly appreciated.
--
Old time COBOL programmer, new VBA programmer.

Merging Excel data from identical copies of a workbook
 

Now, I know this was posted in the Excel Programming section, but it sounds
like Excel's Data Consolidation feature might suit your needs here.

You'd initially need to consolidate from the working sheets into a Master
sheet.

Subsequent consolidations would consolidate both the working sheets and the
Master sheet into a new Master sheet.

Hopefully, I've steered you in the right direction.

Regards,
Ron

Merging Excel data from identical copies of a workbook
 

John,

The following modified code (untested) may resemble the
code you need. Since you are brand new to VBA, your best
bet may be to hire some one to do the coding for you.

'------------------------------------------------------
Sub TransferToMasterSheet() '1st revision
Dim rngMaster As Excel.Range
Dim rngCell As Excel.Range
Dim objWB As Excel.Workbook
Dim objSht As Excel.Worksheet

'The Master workbook sheet must be the active sheet.
'User must select the area on the master sheet.
'Get all the blank cells in the selected cells in the Master sheet.
On Error Resume Next
Set rngMaster = ActiveSheet.Selection.SpecialCells(xlCellTypeBlank s)
On Error GoTo 0

'Identify the other workbook to Excel - use correct name.
Set objWB = Workbooks("WorkbookName")

'If SpecialCells found some blanks then...
If Not rngMaster Is Nothing Then
' Go thru each worksheet in the other workbook.
For Each objSht In objWB.Worksheets
'Transfer the value from the sheet to the master sheet.
For Each rngCell In rngMaster
rngCell.Value = objSht.Range(rngCell.Address).Value
Next 'rngCell
Next 'objSht
End If

'Clean up
Set rngCell = Nothing
Set rngMaster = Nothing
Set objSht = Nothing
Set objWB = Nothing
End Sub
'-----------------------------------------

Regards,
Jim Cone
San Francisco, USA


"John Guzz" wrote in message
...
Jim,
Thanks for the quick reply.
I think that I failed to fully explain my issue:
The Master will be active because I plan to embed the code in it (probably
in a userform) and I also need to automate it so that someone else can simply
execute it.
I also failed to indicate that the workbooks have multiple sheets and that I
need to loop through all of them.

I also need to limit my search to a specific array of cells (excluding such
things as titles).
Your code may already address those issues. If so, I apologize for my lack
of knowledge. Remember "NEWBIE"

John

"Jim Cone" wrote:

John,

The following code may be something like what you need....
'-------------------------------------------------------------
Sub TransferToMasterSheet()
Dim rngMaster As Excel.Range
Dim rngCell As Excel.Range
Dim shtOne As Excel.Worksheet

On Error Resume Next
'Get all the blank cells in the Master Workbook sheet.
'The Master workbook sheet must be the active sheet.
Set rngMaster = ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlank s)

'Specify the workbook and worksheet that the data is taken from.
'The other workbook must be open.
'You must insert the actual names in the line below...
Set shtOne = Workbooks("WorkbookName").Worksheets("SheetName")
On Error GoTo 0

'Go thru each blank cell in the Master sheet and extract data
'from the corresponding cell in the sheet with data.
If Not rngMaster Is Nothing Then
For Each rngCell In rngMaster
rngCell.Value = shtOne.Range(rngCell.Address).Value
Next
End If

'Clean up
Set rngCell = Nothing
Set rngMaster = Nothing
Set shtOne = Nothing
End Sub
'-----------------------------------------------------------------

Regards,
Jim Cone
San Francisco, USA


"John Guzz" wrote in message
...
I have two identical workbooks which must be maintained seperately and merged
occasionaly into a third identical MASTER.xls copy. There is no calculation
involved.

What I need to do is to look at each cell in a range and transfer its data
to the MASTER copy - ONLY if there is nothing in that cell already. If data
exists already in that cell of the the MASTER, I want to do nothing and
progress to the next cell.

I will need to do the same thing with at least one other copy.

The data amounts to a 1 used as a tick mark indicating a procedure performed.
MASTER.xls will ultimately have summaries and charts - that part is easy.

I am an OLD COBOL programmer, but a total newbie when it comes to VBA.
Any assistance would be greatly appreciated.
--
Old time COBOL programmer, new VBA programmer.