Posted By: Anonymous
Dim wkbkdestination As Workbook Dim destsheet As Worksheet For Each ThisWorkSheet In wkbkorigin.Worksheets 'this throws subscript out of range if there is not a sheet in the destination 'workbook that has the same name as the current sheet in the origin workbook. Set destsheet = wkbkdestination.Worksheets(ThisWorkSheet.Name) Next
Basically I loop through all sheets in the origin workbook then set
destsheet in the destination workbook to the sheet with the same name as the currently iterated one in the origin workbook.
How can I test if that sheet exists? Something like:
If wkbkdestination.Worksheets(ThisWorkSheet.Name) Then
Some folk dislike this approach because of an “inappropriate” use of error handling, but I think it’s considered acceptable in VBA… An alternative approach is to loop though all the sheets until you find a match.
Function WorksheetExists(shtName As String, Optional wb As Workbook) As Boolean Dim sht As Worksheet If wb Is Nothing Then Set wb = ThisWorkbook On Error Resume Next Set sht = wb.Sheets(shtName) On Error GoTo 0 WorksheetExists = Not sht Is Nothing End Function