Rowland Hamilton
Active Member
- Joined
- Nov 13, 2009
- Messages
- 250
I modified a code for copying sheet information to a Summary sheet. I keep getting an error with my Endifs and End With Statements. Can you help me?:
Thank you, Rowland
Code:
Sub CopyFromAllSheetsButMaster()
Dim ws As Worksheet
Dim wsMaster As Worksheet
Dim lngMasterLastRow As Long
Dim strData As String
Dim RowCount As Integer
Application.ScreenUpdating = True
'ActiveSheet.Unprotect "kraken" password specific
Set wsMaster = Worksheets("Summary")
lngMasterLastRow = Cells(65536, 1).End(xlUp).Row + 1
' Breaklinks
Call UseBreakLink
For Each ws In Worksheets
If UCase(ws.Name) = "SUMMARY" Then
Else
With ws
ws.Activate
'ActiveSheet.Unprotect "kraken" password specific
Rows("1:7").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("p1").Select
ActiveCell.Formula = "=(65536-COUNTBLANK(A:A)-4)"
RowCount = ActiveCell.Value
If RowCount = "0" Then
Else
'ActiveWorkbook.Unprotect "kraken" password specific
Range(Cells(10, 1), Cells(RowCount + 10, 15)) _
.Copy Destination:=wsMaster.Cells(lngMasterLastRow, 1)
End With
Next ws
Application.ScreenUpdating = True
End Sub
Sub UseBreakLink()
'Keep Subroutine
Dim astrLinks As Variant
On Error GoTo No_Links
' Define variable as an Excel link type.
astrLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
' Break the first link in the active workbook.
For i = 1 To UBound(astrLinks)
ActiveWorkbook.BreakLink _
Name:=astrLinks(i), _
Type:=xlLinkTypeExcelLinks
Next i
No_Links:
End Sub
Thank you, Rowland