Compare and Merge unshared workbooks having exactly Same file structure.

M

mathsbeauty

Board Regular
Joined
Apr 23, 2011
Messages
89
Hello! Some copies of excel workbooks are made using save as option for data entry purpose. Each copy has same file structure because they are created using save as option. Is it possible to merge them (preferably preserving the macros) ? The workbooks are not shared because they were protected sheets and so macros wont run.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Almost anything is possible. What do you mean by "merge"?

Do you want one large workbook containing all the individual worksheets from the various source workbooks?

Or do you want the individual worksheets merged in some way, maybe at the cell level? What if the worksheets have different values in the same cell like this:-

<TABLE style="WIDTH: 242pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=322 align=center border=0><COLGROUP><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" width=88><COL style="WIDTH: 22pt; mso-width-source: userset; mso-width-alt: 1060" width=29><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" width=88><COL style="WIDTH: 22pt; mso-width-source: userset; mso-width-alt: 1060" width=29><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" width=88><TBODY><TR style="HEIGHT: 22.5pt" height=30><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 66pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 22.5pt; BACKGROUND-COLOR: transparent" width=88 height=30>
Workbook 1​
</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 22pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=29>
​
</TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 66pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=88>
Workbook 2​
</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 22pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=29>
​
</TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 66pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=88>
Merged
Workbook​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" width=88 height=17>
Col.A​
</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 22pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=29>
​
</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1" width=88>
Col.A​
</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 22pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=29>
​
</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1" width=88>
Col.A​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=88 height=17>
1​
</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 22pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=29>
​
</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=88>
​
</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 22pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=29>
​
</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=88>
?​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=88 height=17>
2​
</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 22pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=29>
​
</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=88>
​
</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 22pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=29>
​
</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=88>
?​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=88 height=17>
3​
</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 22pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=29>
​
</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=88>
7​
</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 22pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=29>
​
</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=88>
?​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=88 height=17>
​
</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 22pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=29>
​
</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=88>
8​
</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 22pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=29>
​
</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=88>
?​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=88 height=17>
4​
</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 22pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=29>
​
</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=88>
9​
</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 22pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=29>
​
</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=88>
?​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=88 height=17>
5​
</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 22pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=29>
​
</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=88>
​
</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 22pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=29>
​
</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=88>
?​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=88 height=17>
6​
</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 22pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=29>
​
</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=88>
​
</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 22pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=29>
​
</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=88>
?​
</TD></TR></TBODY></TABLE>​

What do you want the merged version to contain?

"Merge" is a very vague word. If you tell us exactly what you want to achieve, preferably with some solid examples, then we may be able to help you.
 
Upvote 0
Thanks for your positive approach. I want merged column as
Col A
1
2
7
8
9
5
6
If the cell is blank, then take data from any one of workbook1 or workbook2. If the data is present in both workbooks then take from second.
I want to merge worksheet1 only of each workbook.
 
Last edited:
Upvote 0
I think this is difficult problem. The option of compare and merging of unshared workbooks is not available. The similar option is available for shared workbooks.
 
Upvote 0
It's not massively difficult but you need some VBA to do it. I think you need to do something like this:-
  • Open both of the workbooks
  • Scan every cell in Workbook1 and if the corresponding cell in Workbook2 is empty, copy the value from Workbook1 to Workbook2
  • Close both of the workbooks
Is that it?
 
Upvote 0
Try this:-
Code: 
[FONT=Fixedsys]Option Explicit[/FONT]
[FONT=Fixedsys] [/FONT]
[FONT=Fixedsys]Public Sub MergeWorkbooks()[/FONT]
[FONT=Fixedsys] [/FONT]
[FONT=Fixedsys]  Dim strWorkbook1 As String[/FONT]
[FONT=Fixedsys]  Dim wb1 As Workbook[/FONT]
[FONT=Fixedsys]  Dim ws1 As Worksheet[/FONT]
[FONT=Fixedsys]  Dim oCell1 As Range[/FONT]
[FONT=Fixedsys] [/FONT]
[FONT=Fixedsys]  Dim strWorkbook2 As String[/FONT]
[FONT=Fixedsys]  Dim wb2 As Workbook[/FONT]
[FONT=Fixedsys]  Dim ws2 As Worksheet[/FONT]
[FONT=Fixedsys]  Dim oCell2 As Range[/FONT]
[FONT=Fixedsys] [/FONT]
[FONT=Fixedsys]  Dim iChanged As Long[/FONT]
[FONT=Fixedsys] [/FONT]
[FONT=Fixedsys]  Dim strMessage As String[/FONT]
[FONT=Fixedsys] [/FONT]
[FONT=Fixedsys]  strWorkbook1 = Application.GetOpenFilename(FileFilter:="Excel workbooks (*.xl*), *.xl*")[/FONT]
[FONT=Fixedsys]  If strWorkbook1 = "False" Then Exit Sub[/FONT]
[FONT=Fixedsys] [/FONT]
[FONT=Fixedsys]  strWorkbook2 = Application.GetOpenFilename(FileFilter:="Excel workbooks (*.xl*), *.xl*")[/FONT]
[FONT=Fixedsys]  If strWorkbook2 = "False" Then Exit Sub[/FONT]
[FONT=Fixedsys] [/FONT]
[FONT=Fixedsys]  Application.ScreenUpdating = False[/FONT]
[FONT=Fixedsys] [/FONT]
[FONT=Fixedsys]  Set wb1 = Workbooks.Open(strWorkbook1)[/FONT]
[FONT=Fixedsys]  Set ws1 = wb1.Sheets(1)[/FONT]
[FONT=Fixedsys] [/FONT]
[FONT=Fixedsys]  Set wb2 = Workbooks.Open(strWorkbook2)[/FONT]
[FONT=Fixedsys]  Set ws2 = wb2.Sheets(1)[/FONT]
[FONT=Fixedsys] [/FONT]
[FONT=Fixedsys]  iChanged = 0[/FONT]
[FONT=Fixedsys]  For Each oCell1 In ws1.UsedRange[/FONT]
[FONT=Fixedsys]    Set oCell2 = ws2.Range(oCell1.Address)[/FONT]
[FONT=Fixedsys]    If IsEmpty(oCell2) Then[/FONT]
[FONT=Fixedsys]      If Not IsEmpty(oCell1) Then[/FONT]
[FONT=Fixedsys]        oCell1.Copy Destination:=oCell2[/FONT]
[FONT=Fixedsys]        iChanged = iChanged + 1[/FONT]
[FONT=Fixedsys]      End If[/FONT]
[FONT=Fixedsys]    End If[/FONT]
[FONT=Fixedsys]  Next oCell1[/FONT]
[FONT=Fixedsys] [/FONT]
[FONT=Fixedsys]  Application.ScreenUpdating = True[/FONT]
[FONT=Fixedsys] [/FONT]
[FONT=Fixedsys]  strMessage = vbCrLf _[/FONT]
[FONT=Fixedsys]       & "Values from " & wb1.Name & " have been overlaid onto " & wb2.Name & "." _[/FONT]
[FONT=Fixedsys]       & Space(10) & vbCrLf & vbCrLf _[/FONT]
[FONT=Fixedsys]       & "Number of cells updated: " & iChanged _[/FONT]
[FONT=Fixedsys]       & Space(10) & vbCrLf & vbCrLf _[/FONT]
[FONT=Fixedsys]       & "Please save " & wb2.Name & " if you want to preserve these changes." _[/FONT]
[FONT=Fixedsys]       & Space(10)[/FONT]
[FONT=Fixedsys] [/FONT]
[FONT=Fixedsys]  wb1.Close savechanges:=False[/FONT]
[FONT=Fixedsys] [/FONT]
[FONT=Fixedsys]  MsgBox strMessage, vbOKOnly + vbExclamation[/FONT]
[FONT=Fixedsys] [/FONT]
[FONT=Fixedsys]End Sub[/FONT]
Open a new workbook, paste this code into a new general code module and run it.

It will prompt you to open two workbooks, then it will copy the cells from workbook1.sheet1 to workbook2.sheet1 if the cell in workbook2.sheet1 is empty.

Workbook1 is not changed: all the changes are made to workbook2 but workbook2 isn't saved back to disk - you have to do that manually. Any macros in workbook2 are left untouched.

See if that's any use to you. Shout if you need any changes.
 
Upvote 0
Sorry, I meant to say: test this routine with copies of your workbooks.

Also (obviously) check the code does exactly what you want - that it copies all of the cells you're expecting it to and doesn't cange anything you don't want changed.
 
Upvote 0
Thanks Ruddles for all your efforts! You have done exactly what I was looking for. It worked like magic! You are great! Thanks once again.
 
Upvote 0
I forgot to see how old this post was, but I have a question if you still check back here.
I have used this, and it only merges the first worksheet, I have a workbook with 15 work sheets and was wondering how i can get this to merge all work sheets.

Thank you for your time,
Ron

Ruddles said:
Try this:-
Code: 
[FONT=Fixedsys]Option Explicit[/FONT]

[FONT=Fixedsys]Public Sub MergeWorkbooks()[/FONT]

[FONT=Fixedsys]  Dim strWorkbook1 As String[/FONT]
[FONT=Fixedsys]  Dim wb1 As Workbook[/FONT]
[FONT=Fixedsys]  Dim ws1 As Worksheet[/FONT]
[FONT=Fixedsys]  Dim oCell1 As Range[/FONT]

[FONT=Fixedsys]  Dim strWorkbook2 As String[/FONT]
[FONT=Fixedsys]  Dim wb2 As Workbook[/FONT]
[FONT=Fixedsys]  Dim ws2 As Worksheet[/FONT]
[FONT=Fixedsys]  Dim oCell2 As Range[/FONT]

[FONT=Fixedsys]  Dim iChanged As Long[/FONT]

[FONT=Fixedsys]  Dim strMessage As String[/FONT]

[FONT=Fixedsys]  strWorkbook1 = Application.GetOpenFilename(FileFilter:="Excel workbooks (*.xl*), *.xl*")[/FONT]
[FONT=Fixedsys]  If strWorkbook1 = "False" Then Exit Sub[/FONT]

[FONT=Fixedsys]  strWorkbook2 = Application.GetOpenFilename(FileFilter:="Excel workbooks (*.xl*), *.xl*")[/FONT]
[FONT=Fixedsys]  If strWorkbook2 = "False" Then Exit Sub[/FONT]

[FONT=Fixedsys]  Application.ScreenUpdating = False[/FONT]

[FONT=Fixedsys]  Set wb1 = Workbooks.Open(strWorkbook1)[/FONT]
[FONT=Fixedsys]  Set ws1 = wb1.Sheets(1)[/FONT]

[FONT=Fixedsys]  Set wb2 = Workbooks.Open(strWorkbook2)[/FONT]
[FONT=Fixedsys]  Set ws2 = wb2.Sheets(1)[/FONT]

[FONT=Fixedsys]  iChanged = 0[/FONT]
[FONT=Fixedsys]  For Each oCell1 In ws1.UsedRange[/FONT]
[FONT=Fixedsys]    Set oCell2 = ws2.Range(oCell1.Address)[/FONT]
[FONT=Fixedsys]    If IsEmpty(oCell2) Then[/FONT]
[FONT=Fixedsys]      If Not IsEmpty(oCell1) Then[/FONT]
[FONT=Fixedsys]        oCell1.Copy Destination:=oCell2[/FONT]
[FONT=Fixedsys]        iChanged = iChanged + 1[/FONT]
[FONT=Fixedsys]      End If[/FONT]
[FONT=Fixedsys]    End If[/FONT]
[FONT=Fixedsys]  Next oCell1[/FONT]

[FONT=Fixedsys]  Application.ScreenUpdating = True[/FONT]

[FONT=Fixedsys]  strMessage = vbCrLf _[/FONT]
[FONT=Fixedsys]       & "Values from " & wb1.Name & " have been overlaid onto " & wb2.Name & "." _[/FONT]
[FONT=Fixedsys]       & Space(10) & vbCrLf & vbCrLf _[/FONT]
[FONT=Fixedsys]       & "Number of cells updated: " & iChanged _[/FONT]
[FONT=Fixedsys]       & Space(10) & vbCrLf & vbCrLf _[/FONT]
[FONT=Fixedsys]       & "Please save " & wb2.Name & " if you want to preserve these changes." _[/FONT]
[FONT=Fixedsys]       & Space(10)[/FONT]

[FONT=Fixedsys]  wb1.Close savechanges:=False[/FONT]

[FONT=Fixedsys]  MsgBox strMessage, vbOKOnly + vbExclamation[/FONT]

[FONT=Fixedsys]End Sub[/FONT]
Open a new workbook, paste this code into a new general code module and run it.

It will prompt you to open two workbooks, then it will copy the cells from workbook1.sheet1 to workbook2.sheet1 if the cell in workbook2.sheet1 is empty.

Workbook1 is not changed: all the changes are made to workbook2 but workbook2 isn't saved back to disk - you have to do that manually. Any macros in workbook2 are left untouched.

See if that's any use to you. Shout if you need any changes.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
writing a macro to copy an entire xlsm workbook to a users desktop as xlsx values only.
Replies
9
Views
709
Alex Blakenburg
A
D
  • Question Question
Modify a worksheet without registering it as a change
Replies
1
Views
109
Kaper
Kaper
E
  • Question Question
I have 1 UserForm and want to save to different sheets in the same workbook
2
Replies
10
Views
306
Ellendigitl00
E
T
  • Question Question
Automatically Copying Data To A New Workbook When Closing A Workbook
Replies
4
Views
468
Tmini
T
A
  • Question Question
Purchase Order macros
Replies
5
Views
353
AndyB86
A

Forum statistics

Threads
1,224,603
Messages
6,179,854
Members
452,948
Latest member
UsmanAli786

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top