Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merging identical workbooks in Excel 2007 | Excel Discussion (Misc queries) | |||
Merging two sheets with non-identical data | Excel Discussion (Misc queries) | |||
Excel opening 2 identical & parallel copies of a file. Won't stop | Excel Discussion (Misc queries) | |||
Merging to identical Excel files | Excel Discussion (Misc queries) | |||
merge/extract data from identical worksheets in a workbook | Excel Discussion (Misc queries) |