I keep getting supscript out of range error how do I solve?

**desmarai** · 05-02-2006, 12:23 PM

Sub ImportCarryLists()
'
' ImportCarryLists Macro
' Macro created 2000/07/07
'

'
MsgBox "Please select the previous month's file for importing Carry Lists and forwarding balances.", vbOKOnly, FileOpen
CarryListFile = Application.GetOpenFilename
Workbooks.Open _
FileName:=CarryListFile
Sheets(Array("JE Dental", "DIV 10", "DIV 11", "DIV 12", "DIV 14", "DIV 20", "DIV 21", _
"DIV 30", "DIV 31", "DIV 32", "DIV 42", "DIV 43", "DIV 50", "DIV 51", "DIV 80", _
"DIV 84", "Adjustments")).Copy After:=Workbooks(1).Sheets("Dental Report")
Worksheets("DIV 10").Unprotect
Worksheets("DIV 11").Unprotect
Worksheets("DIV 12").Unprotect
Worksheets("DIV 14").Unprotect
Worksheets("DIV 20").Unprotect
Worksheets("DIV 21").Unprotect
Worksheets("DIV 30").Unprotect
Worksheets("DIV 31").Unprotect
Worksheets("DIV 32").Unprotect
Worksheets("DIV 42").Unprotect
Worksheets("DIV 43").Unprotect
Worksheets("DIV 50").Unprotect
Worksheets("DIV 51").Unprotect
Worksheets("DIV 80").Unprotect
Worksheets("DIV 84").Unprotect
Sheets("PivotTable").Select
End Sub
Sub InsertFormulaHeadings()

' inputs formulas into Corrected Count and
' Carry List columns

Sheets("PivotTable").Select
Range("F3:F6").FormulaR1C1 = "=IF(R[1]C[-1]=0,0,RC[-1])"
Range("F7:F122").FormulaR1C1 = "=IF(R[1]C[-2]=0,0,IF(RC[-3]="""",IF(R[1]C[-3]="""",R[-2]C+R[-4]C+R[-6]C,R[-2]C+R[-4]C+R[-6]C),RC[-2]))"
Range("F1").FormulaR1C1 = "Current Month"
Range("F2").FormulaR1C1 = "Corrected Count"
Range("G2").FormulaR1C1 = "Carry List"
Range("H2").FormulaR1C1 = "Adjust"
Range("I2").FormulaR1C1 = "Total"
Range("J1").FormulaR1C1 = "Previous Month"
Range("J2").FormulaR1C1 = "Bal Fwd"
Range("K2").FormulaR1C1 = "New"
Range("L2").FormulaR1C1 = "Left"
Range("M2").FormulaR1C1 = "Adjust"
Range("N2").FormulaR1C1 = "New Bal"
Range("O2").FormulaR1C1 = "Audit"
Range("F1:I1").Select
With Selection
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
Range("J1:N1").Select
With Selection
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
Range("F1:I2").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("J1:N2").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("O1:O2").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End Sub

Ardus Petus · 05-02-2006, 01:10 PM

What you need requests some VBA code.
Interested?

HTA
--
AP

"desmarai" <[email protected]> a écrit
dans le message de news:
[email protected]...
>
> Sub ImportCarryLists()
> '
> ' ImportCarryLists Macro
> ' Macro created 2000/07/07
> '
>
> '
> MsgBox "Please select the previous month's file for importing Carry
> Lists and forwarding balances.", vbOKOnly, FileOpen
> CarryListFile = Application.GetOpenFilename
> Workbooks.Open _
> FileName:=CarryListFile
> **Sheets(Array("JE Dental", "DIV 10", "DIV 11", "DIV 12", "DIV 14",
> "DIV 20", "DIV 21", _
> "DIV 30", "DIV 31", "DIV 32", "DIV 42", "DIV 43", "DIV 50",
> "DIV 51", "DIV 80", _
> "DIV 84", "Adjustments")).Copy
> After:=Workbooks(1).Sheets("Dental Report")**
> Worksheets("DIV 10").Unprotect
> Worksheets("DIV 11").Unprotect
> Worksheets("DIV 12").Unprotect
> Worksheets("DIV 14").Unprotect
> Worksheets("DIV 20").Unprotect
> Worksheets("DIV 21").Unprotect
> Worksheets("DIV 30").Unprotect
> Worksheets("DIV 31").Unprotect
> Worksheets("DIV 32").Unprotect
> Worksheets("DIV 42").Unprotect
> Worksheets("DIV 43").Unprotect
> Worksheets("DIV 50").Unprotect
> Worksheets("DIV 51").Unprotect
> Worksheets("DIV 80").Unprotect
> Worksheets("DIV 84").Unprotect
> Sheets("PivotTable").Select
> End Sub
> Sub InsertFormulaHeadings()
>
> ' inputs formulas into Corrected Count and
> ' Carry List columns
>
> Sheets("PivotTable").Select
> Range("F3:F6").FormulaR1C1 = "=IF(R[1]C[-1]=0,0,RC[-1])"
> Range("F7:F122").FormulaR1C1 =
> "=IF(R[1]C[-2]=0,0,IF(RC[-3]="""",IF(R[1]C[-3]="""",R[-2]C+R[-4]C+R[-6]C,R[-2]C+R[-4]C+R[-6]C),RC[-2]))"
> Range("F1").FormulaR1C1 = "Current Month"
> Range("F2").FormulaR1C1 = "Corrected Count"
> Range("G2").FormulaR1C1 = "Carry List"
> Range("H2").FormulaR1C1 = "Adjust"
> Range("I2").FormulaR1C1 = "Total"
> Range("J1").FormulaR1C1 = "Previous Month"
> Range("J2").FormulaR1C1 = "Bal Fwd"
> Range("K2").FormulaR1C1 = "New"
> Range("L2").FormulaR1C1 = "Left"
> Range("M2").FormulaR1C1 = "Adjust"
> Range("N2").FormulaR1C1 = "New Bal"
> Range("O2").FormulaR1C1 = "Audit"
> Range("F1:I1").Select
> With Selection
> HorizontalAlignment = xlCenterAcrossSelection
> VerticalAlignment = xlBottom
> WrapText = False
> Orientation = 0
> ShrinkToFit = False
> MergeCells = False
> End With
> Range("J1:N1").Select
> With Selection
> HorizontalAlignment = xlCenterAcrossSelection
> VerticalAlignment = xlBottom
> WrapText = False
> Orientation = 0
> ShrinkToFit = False
> MergeCells = False
> End With
> Range("F1:I2").Select
> Selection.Borders(xlDiagonalDown).LineStyle = xlNone
> Selection.Borders(xlDiagonalUp).LineStyle = xlNone
> With Selection.Borders(xlEdgeLeft)
> LineStyle = xlContinuous
> Weight = xlThin
> ColorIndex = xlAutomatic
> End With
> With Selection.Borders(xlEdgeTop)
> LineStyle = xlContinuous
> Weight = xlThin
> ColorIndex = xlAutomatic
> End With
> With Selection.Borders(xlEdgeBottom)
> LineStyle = xlContinuous
> Weight = xlThin
> ColorIndex = xlAutomatic
> End With
> With Selection.Borders(xlEdgeRight)
> LineStyle = xlContinuous
> Weight = xlThin
> ColorIndex = xlAutomatic
> End With
> Selection.Borders(xlInsideVertical).LineStyle = xlNone
> Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
> Range("J1:N2").Select
> Selection.Borders(xlDiagonalDown).LineStyle = xlNone
> Selection.Borders(xlDiagonalUp).LineStyle = xlNone
> With Selection.Borders(xlEdgeLeft)
> LineStyle = xlContinuous
> Weight = xlThin
> ColorIndex = xlAutomatic
> End With
> With Selection.Borders(xlEdgeTop)
> LineStyle = xlContinuous
> Weight = xlThin
> ColorIndex = xlAutomatic
> End With
> With Selection.Borders(xlEdgeBottom)
> LineStyle = xlContinuous
> Weight = xlThin
> ColorIndex = xlAutomatic
> End With
> With Selection.Borders(xlEdgeRight)
> LineStyle = xlContinuous
> Weight = xlThin
> ColorIndex = xlAutomatic
> End With
> Selection.Borders(xlInsideVertical).LineStyle = xlNone
> Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
> Range("O1:O2").Select
> Selection.Borders(xlDiagonalDown).LineStyle = xlNone
> Selection.Borders(xlDiagonalUp).LineStyle = xlNone
> With Selection.Borders(xlEdgeLeft)
> LineStyle = xlContinuous
> Weight = xlThin
> ColorIndex = xlAutomatic
> End With
> With Selection.Borders(xlEdgeTop)
> LineStyle = xlContinuous
> Weight = xlThin
> ColorIndex = xlAutomatic
> End With
> With Selection.Borders(xlEdgeBottom)
> LineStyle = xlContinuous
> Weight = xlThin
> ColorIndex = xlAutomatic
> End With
> With Selection.Borders(xlEdgeRight)
> LineStyle = xlContinuous
> Weight = xlThin
> ColorIndex = xlAutomatic
> End With
> Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
> End Sub
>
>
> --
> desmarai
> ------------------------------------------------------------------------
> desmarai's Profile:
> http://www.excelforum.com/member.php...o&userid=34048
> View this thread: http://www.excelforum.com/showthread...hreadid=538123
>

Ardus Petus · 05-02-2006, 01:15 PM

Sorry: I didn't read your msg completly.
--
AP

"Ardus Petus" <[email protected]> a écrit dans le message de news:
%[email protected]...
> What you need requests some VBA code.
> Interested?
>
> HTA
> --
> AP
>
> "desmarai" <[email protected]> a écrit
> dans le message de news:
> [email protected]...
>>
>> Sub ImportCarryLists()
>> '
>> ' ImportCarryLists Macro
>> ' Macro created 2000/07/07
>> '
>>
>> '
>> MsgBox "Please select the previous month's file for importing Carry
>> Lists and forwarding balances.", vbOKOnly, FileOpen
>> CarryListFile = Application.GetOpenFilename
>> Workbooks.Open _
>> FileName:=CarryListFile
>> **Sheets(Array("JE Dental", "DIV 10", "DIV 11", "DIV 12", "DIV 14",
>> "DIV 20", "DIV 21", _
>> "DIV 30", "DIV 31", "DIV 32", "DIV 42", "DIV 43", "DIV 50",
>> "DIV 51", "DIV 80", _
>> "DIV 84", "Adjustments")).Copy
>> After:=Workbooks(1).Sheets("Dental Report")**
>> Worksheets("DIV 10").Unprotect
>> Worksheets("DIV 11").Unprotect
>> Worksheets("DIV 12").Unprotect
>> Worksheets("DIV 14").Unprotect
>> Worksheets("DIV 20").Unprotect
>> Worksheets("DIV 21").Unprotect
>> Worksheets("DIV 30").Unprotect
>> Worksheets("DIV 31").Unprotect
>> Worksheets("DIV 32").Unprotect
>> Worksheets("DIV 42").Unprotect
>> Worksheets("DIV 43").Unprotect
>> Worksheets("DIV 50").Unprotect
>> Worksheets("DIV 51").Unprotect
>> Worksheets("DIV 80").Unprotect
>> Worksheets("DIV 84").Unprotect
>> Sheets("PivotTable").Select
>> End Sub
>> Sub InsertFormulaHeadings()
>>
>> ' inputs formulas into Corrected Count and
>> ' Carry List columns
>>
>> Sheets("PivotTable").Select
>> Range("F3:F6").FormulaR1C1 = "=IF(R[1]C[-1]=0,0,RC[-1])"
>> Range("F7:F122").FormulaR1C1 =
>> "=IF(R[1]C[-2]=0,0,IF(RC[-3]="""",IF(R[1]C[-3]="""",R[-2]C+R[-4]C+R[-6]C,R[-2]C+R[-4]C+R[-6]C),RC[-2]))"
>> Range("F1").FormulaR1C1 = "Current Month"
>> Range("F2").FormulaR1C1 = "Corrected Count"
>> Range("G2").FormulaR1C1 = "Carry List"
>> Range("H2").FormulaR1C1 = "Adjust"
>> Range("I2").FormulaR1C1 = "Total"
>> Range("J1").FormulaR1C1 = "Previous Month"
>> Range("J2").FormulaR1C1 = "Bal Fwd"
>> Range("K2").FormulaR1C1 = "New"
>> Range("L2").FormulaR1C1 = "Left"
>> Range("M2").FormulaR1C1 = "Adjust"
>> Range("N2").FormulaR1C1 = "New Bal"
>> Range("O2").FormulaR1C1 = "Audit"
>> Range("F1:I1").Select
>> With Selection
>> HorizontalAlignment = xlCenterAcrossSelection
>> VerticalAlignment = xlBottom
>> WrapText = False
>> Orientation = 0
>> ShrinkToFit = False
>> MergeCells = False
>> End With
>> Range("J1:N1").Select
>> With Selection
>> HorizontalAlignment = xlCenterAcrossSelection
>> VerticalAlignment = xlBottom
>> WrapText = False
>> Orientation = 0
>> ShrinkToFit = False
>> MergeCells = False
>> End With
>> Range("F1:I2").Select
>> Selection.Borders(xlDiagonalDown).LineStyle = xlNone
>> Selection.Borders(xlDiagonalUp).LineStyle = xlNone
>> With Selection.Borders(xlEdgeLeft)
>> LineStyle = xlContinuous
>> Weight = xlThin
>> ColorIndex = xlAutomatic
>> End With
>> With Selection.Borders(xlEdgeTop)
>> LineStyle = xlContinuous
>> Weight = xlThin
>> ColorIndex = xlAutomatic
>> End With
>> With Selection.Borders(xlEdgeBottom)
>> LineStyle = xlContinuous
>> Weight = xlThin
>> ColorIndex = xlAutomatic
>> End With
>> With Selection.Borders(xlEdgeRight)
>> LineStyle = xlContinuous
>> Weight = xlThin
>> ColorIndex = xlAutomatic
>> End With
>> Selection.Borders(xlInsideVertical).LineStyle = xlNone
>> Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
>> Range("J1:N2").Select
>> Selection.Borders(xlDiagonalDown).LineStyle = xlNone
>> Selection.Borders(xlDiagonalUp).LineStyle = xlNone
>> With Selection.Borders(xlEdgeLeft)
>> LineStyle = xlContinuous
>> Weight = xlThin
>> ColorIndex = xlAutomatic
>> End With
>> With Selection.Borders(xlEdgeTop)
>> LineStyle = xlContinuous
>> Weight = xlThin
>> ColorIndex = xlAutomatic
>> End With
>> With Selection.Borders(xlEdgeBottom)
>> LineStyle = xlContinuous
>> Weight = xlThin
>> ColorIndex = xlAutomatic
>> End With
>> With Selection.Borders(xlEdgeRight)
>> LineStyle = xlContinuous
>> Weight = xlThin
>> ColorIndex = xlAutomatic
>> End With
>> Selection.Borders(xlInsideVertical).LineStyle = xlNone
>> Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
>> Range("O1:O2").Select
>> Selection.Borders(xlDiagonalDown).LineStyle = xlNone
>> Selection.Borders(xlDiagonalUp).LineStyle = xlNone
>> With Selection.Borders(xlEdgeLeft)
>> LineStyle = xlContinuous
>> Weight = xlThin
>> ColorIndex = xlAutomatic
>> End With
>> With Selection.Borders(xlEdgeTop)
>> LineStyle = xlContinuous
>> Weight = xlThin
>> ColorIndex = xlAutomatic
>> End With
>> With Selection.Borders(xlEdgeBottom)
>> LineStyle = xlContinuous
>> Weight = xlThin
>> ColorIndex = xlAutomatic
>> End With
>> With Selection.Borders(xlEdgeRight)
>> LineStyle = xlContinuous
>> Weight = xlThin
>> ColorIndex = xlAutomatic
>> End With
>> Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
>> End Sub
>>
>>
>> --
>> desmarai
>> ------------------------------------------------------------------------
>> desmarai's Profile:
>> http://www.excelforum.com/member.php...o&userid=34048
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=538123
>>
>
>

Dave Peterson · 05-02-2006, 01:25 PM

Then one of those valuses isn't a name of a sheet in that workbook.

Maybe it's a typo?

If it's not a typo and sometimes some of the sheets are indeed missing, you can
change your code to check for missing sheets and not process them.

desmarai wrote:
>
> Sub ImportCarryLists()
> '
> ' ImportCarryLists Macro
> ' Macro created 2000/07/07
> '
>
> '
> MsgBox "Please select the previous month's file for importing Carry
> Lists and forwarding balances.", vbOKOnly, FileOpen
> CarryListFile = Application.GetOpenFilename
> Workbooks.Open _
> FileName:=CarryListFile
> **Sheets(Array("JE Dental", "DIV 10", "DIV 11", "DIV 12", "DIV 14",
> "DIV 20", "DIV 21", _
> "DIV 30", "DIV 31", "DIV 32", "DIV 42", "DIV 43", "DIV 50",
> "DIV 51", "DIV 80", _
> "DIV 84", "Adjustments")).Copy
> After:=Workbooks(1).Sheets("Dental Report")**
> Worksheets("DIV 10").Unprotect
> Worksheets("DIV 11").Unprotect
> Worksheets("DIV 12").Unprotect
> Worksheets("DIV 14").Unprotect
> Worksheets("DIV 20").Unprotect
> Worksheets("DIV 21").Unprotect
> Worksheets("DIV 30").Unprotect
> Worksheets("DIV 31").Unprotect
> Worksheets("DIV 32").Unprotect
> Worksheets("DIV 42").Unprotect
> Worksheets("DIV 43").Unprotect
> Worksheets("DIV 50").Unprotect
> Worksheets("DIV 51").Unprotect
> Worksheets("DIV 80").Unprotect
> Worksheets("DIV 84").Unprotect
> Sheets("PivotTable").Select
> End Sub
> Sub InsertFormulaHeadings()
>
> ' inputs formulas into Corrected Count and
> ' Carry List columns
>
> Sheets("PivotTable").Select
> Range("F3:F6").FormulaR1C1 = "=IF(R[1]C[-1]=0,0,RC[-1])"
> Range("F7:F122").FormulaR1C1 =
> "=IF(R[1]C[-2]=0,0,IF(RC[-3]="""",IF(R[1]C[-3]="""",R[-2]C+R[-4]C+R[-6]C,R[-2]C+R[-4]C+R[-6]C),RC[-2]))"
> Range("F1").FormulaR1C1 = "Current Month"
> Range("F2").FormulaR1C1 = "Corrected Count"
> Range("G2").FormulaR1C1 = "Carry List"
> Range("H2").FormulaR1C1 = "Adjust"
> Range("I2").FormulaR1C1 = "Total"
> Range("J1").FormulaR1C1 = "Previous Month"
> Range("J2").FormulaR1C1 = "Bal Fwd"
> Range("K2").FormulaR1C1 = "New"
> Range("L2").FormulaR1C1 = "Left"
> Range("M2").FormulaR1C1 = "Adjust"
> Range("N2").FormulaR1C1 = "New Bal"
> Range("O2").FormulaR1C1 = "Audit"
> Range("F1:I1").Select
> With Selection
> HorizontalAlignment = xlCenterAcrossSelection
> VerticalAlignment = xlBottom
> WrapText = False
> Orientation = 0
> ShrinkToFit = False
> MergeCells = False
> End With
> Range("J1:N1").Select
> With Selection
> HorizontalAlignment = xlCenterAcrossSelection
> VerticalAlignment = xlBottom
> WrapText = False
> Orientation = 0
> ShrinkToFit = False
> MergeCells = False
> End With
> Range("F1:I2").Select
> Selection.Borders(xlDiagonalDown).LineStyle = xlNone
> Selection.Borders(xlDiagonalUp).LineStyle = xlNone
> With Selection.Borders(xlEdgeLeft)
> LineStyle = xlContinuous
> Weight = xlThin
> ColorIndex = xlAutomatic
> End With
> With Selection.Borders(xlEdgeTop)
> LineStyle = xlContinuous
> Weight = xlThin
> ColorIndex = xlAutomatic
> End With
> With Selection.Borders(xlEdgeBottom)
> LineStyle = xlContinuous
> Weight = xlThin
> ColorIndex = xlAutomatic
> End With
> With Selection.Borders(xlEdgeRight)
> LineStyle = xlContinuous
> Weight = xlThin
> ColorIndex = xlAutomatic
> End With
> Selection.Borders(xlInsideVertical).LineStyle = xlNone
> Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
> Range("J1:N2").Select
> Selection.Borders(xlDiagonalDown).LineStyle = xlNone
> Selection.Borders(xlDiagonalUp).LineStyle = xlNone
> With Selection.Borders(xlEdgeLeft)
> LineStyle = xlContinuous
> Weight = xlThin
> ColorIndex = xlAutomatic
> End With
> With Selection.Borders(xlEdgeTop)
> LineStyle = xlContinuous
> Weight = xlThin
> ColorIndex = xlAutomatic
> End With
> With Selection.Borders(xlEdgeBottom)
> LineStyle = xlContinuous
> Weight = xlThin
> ColorIndex = xlAutomatic
> End With
> With Selection.Borders(xlEdgeRight)
> LineStyle = xlContinuous
> Weight = xlThin
> ColorIndex = xlAutomatic
> End With
> Selection.Borders(xlInsideVertical).LineStyle = xlNone
> Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
> Range("O1:O2").Select
> Selection.Borders(xlDiagonalDown).LineStyle = xlNone
> Selection.Borders(xlDiagonalUp).LineStyle = xlNone
> With Selection.Borders(xlEdgeLeft)
> LineStyle = xlContinuous
> Weight = xlThin
> ColorIndex = xlAutomatic
> End With
> With Selection.Borders(xlEdgeTop)
> LineStyle = xlContinuous
> Weight = xlThin
> ColorIndex = xlAutomatic
> End With
> With Selection.Borders(xlEdgeBottom)
> LineStyle = xlContinuous
> Weight = xlThin
> ColorIndex = xlAutomatic
> End With
> With Selection.Borders(xlEdgeRight)
> LineStyle = xlContinuous
> Weight = xlThin
> ColorIndex = xlAutomatic
> End With
> Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
> End Sub
>
> --
> desmarai
> ------------------------------------------------------------------------
> desmarai's Profile: http://www.excelforum.com/member.php...o&userid=34048
> View this thread: http://www.excelforum.com/showthread...hreadid=538123

--

Dave Peterson

**desmarai** · 05-02-2006, 02:07 PM

Hi, thanks,

I'll check for a typo; however, how do you check for missing sheets and not process as you stated below (in case I need that)?

If it's not a typo and sometimes some of the sheets are indeed missing, you
can change your code to check for missing sheets and not process them.

Inge

Dave Peterson · 05-02-2006, 02:20 PM

Option Explicit
sub Testme()
Dim mySheetNames as variant
dim iCtr as long
mySheetNames = Array("JE Dental", "DIV 10", "DIV 11", "DIV 12", "DIV 14", _
"DIV 20", "DIV 21", "DIV 30", "DIV 31", "DIV 32", _
"DIV 42", "DIV 43", "DIV 50", "DIV 51", "DIV 80", _
"DIV 84", "Adjustments")

for ictr = lbound(Mysheetnames) to ubound(MySheetNames)
msgbox worksheetexists(mySheetNames(ictr),activeworkbook)
if worksheetexists(mysheetnames(ictr), activeworkbook) then
'do the work/copy
else
'skip it or a msgbox?
end if
next ictr

End Sub

Function WorksheetExists(SheetName As Variant, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0)
End Function

desmarai wrote:
>
> Hi, thanks,
>
> I'll check for a typo; however, how do you check for missing sheets and
> not process as you stated below (in case I need that)?
>
> If it's not a typo and sometimes some of the sheets are indeed missing,
> you
> can change your code to check for missing sheets and not process them.
>
> Inge
>
> --
> desmarai
> ------------------------------------------------------------------------
> desmarai's Profile: http://www.excelforum.com/member.php...o&userid=34048
> View this thread: http://www.excelforum.com/showthread...hreadid=538123

--

Dave Peterson