Hoopsah
11-30-2012, 04:30 AM
Hi
I have copied this code from elsewhere:
Function wsExists(wksName As String) As Boolean
On Error Resume Next
wsExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function
Sub Combine()
Dim destSH As Worksheet, sh As Worksheet
Dim rw As Long
Application.DisplayAlerts = False
If wsExists("Summary") Then
Sheets("Summary").Delete
End If
Application.DisplayAlerts = True
Application.ScreenUpdating = False
Set destSH = Worksheets.Add(After:=Worksheets(Worksheets.Count))
destSH.Name = "Summary" '<----- This will add a sheet named "Summary".
rw = 2
For Each sh In Worksheets
If sh.Name <> destSH.Name Then
sh.Range("A28:Z28").Copy
With destSH.Cells(rw, 1)
.PasteSpecial Paste:=xlPasteValues
End With
rw = rw + 2
End If
Next sh
Application.ScreenUpdating = True
End Sub
However, I cant quite get it to work properly.
I am looking for it to copy a specific range of worksheets into 1 summary worksheet, columns begin on A28 across to Z28 but will have varying rows on each worksheet.
The other thing is that I don't want the first 7 tabs copied, all of the ones I want will begin with tab name "WE xxxxxxx"
All help appreciated
I have copied this code from elsewhere:
Function wsExists(wksName As String) As Boolean
On Error Resume Next
wsExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function
Sub Combine()
Dim destSH As Worksheet, sh As Worksheet
Dim rw As Long
Application.DisplayAlerts = False
If wsExists("Summary") Then
Sheets("Summary").Delete
End If
Application.DisplayAlerts = True
Application.ScreenUpdating = False
Set destSH = Worksheets.Add(After:=Worksheets(Worksheets.Count))
destSH.Name = "Summary" '<----- This will add a sheet named "Summary".
rw = 2
For Each sh In Worksheets
If sh.Name <> destSH.Name Then
sh.Range("A28:Z28").Copy
With destSH.Cells(rw, 1)
.PasteSpecial Paste:=xlPasteValues
End With
rw = rw + 2
End If
Next sh
Application.ScreenUpdating = True
End Sub
However, I cant quite get it to work properly.
I am looking for it to copy a specific range of worksheets into 1 summary worksheet, columns begin on A28 across to Z28 but will have varying rows on each worksheet.
The other thing is that I don't want the first 7 tabs copied, all of the ones I want will begin with tab name "WE xxxxxxx"
All help appreciated