Merging Worksheets in Excel

  • Thread starter Thread starter TommyJ
  • Start date Start date
T

TommyJ

I can merge the worksheets with no problem, but on the worksheet I'm
updating from the others, there's a column at the end that gets
overwritten every time and I want it to remain static. Any ideas?
Here's the code I'm using for the merge which was gratefully clipped
from http://www.rondebruin.nl/copy2.htm

Private Sub CommandButton1_Click()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Application.ScreenUpdating = False
Set DestSh = Worksheets("Merged")
Worksheets("Merged").UsedRange.Offset(4,0).ClearContents
For Each sh In Sheets(Array("Sheet2", "Sheet3"))
LastR = LastRow(DestSh)
sh.UsedRange.Offset(4, 0).Copy DestSh.Cells(LastR + 1, 1)
Next
Application.ScreenUpdating = True
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Function LastCol(sh As Worksheet)
On Error Resume Next
LastCol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function
 
is the column in the sheet before any copying? If so, you woulr clear it
with

Worksheets("Merged").UsedRange.Offset(4,0).ClearContents


You would need to use

set rng = worksheets("Merged").UsedRange.Offset(4,0)
Intersect(rng,worksheets("Merged").Columns(1).Resize(10)).Clearcontents

Change the 10 to reflect the number of columns you want cleared starting
from column A.
 
Back
Top