Nested With's
 

I'm having problems with this code (I've include more than I need to just to
show it is all initialised properly)...
Dim wb As Workbook
Dim wss As Worksheet, wss As Worksheet
Set wb = ActiveWorkbook
Application.DisplayAlerts = False
If SheetExists(BuM) Then
Sheets(BuM).Delete
End If
Application.DisplayAlerts = True
On Error Resume Next
Set wbCVR = Workbooks("CVR " & BuM & ".xls")
'Have I got file open?
If wbCVR Is Nothing Then
'No - open it
On Error GoTo 0
Workbooks.Open Filename:=".\CVR " & BuM & ".xls", UpdateLinks:=0,
ReadOnly:=True, Notify:=False
Set wsd = ActiveWorkbook.Worksheets("Detail Sheet")
Set wss = ActiveWorkbook.Worksheets("Summary")
Else
wbCVR.Activate
'Yes - use it
Set wsd = wbCVR.Worksheets("Detail Sheet")
Set wss = wbCVR.Worksheets("Summary")
End If
Application.DisplayAlerts = False
With wb
.Worksheets.Add befo=.Worksheets("Pivot Table")
With .ActiveSheet
.name = BuM
.Tab.ColorIndex = sc
wsd.Cells.Copy .Cells
End With
wss.Range("CopyOut").Copy
.Worksheets("Summary by BUM's").Cells(5, x).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats 'THIS LINE WORKS Data in the right place
Application.CutCopyMode = False
.Worksheets(BuM).Select 'PROBLEM HERE! SELECT METHOD FAILED
Cells.Select
Selection.EntireRow.Hidden = False
End With

Start with summary workbok open, delete the old data sheet, insert new sheet
rename it, open another workbook and copy data from it, paste into worksheet
in first workbook, then swap to recentlycreated worksheet, now it fails, Why?

Nested With's
 

quick guess,
you dim wss twice and don't dim wsd. Could this be the problem?

"Graham Y" wrote:

I'm having problems with this code (I've include more than I need to just to
show it is all initialised properly)...
Dim wb As Workbook
Dim wss As Worksheet, wss As Worksheet
Set wb = ActiveWorkbook
Application.DisplayAlerts = False
If SheetExists(BuM) Then
Sheets(BuM).Delete
End If
Application.DisplayAlerts = True
On Error Resume Next
Set wbCVR = Workbooks("CVR " & BuM & ".xls")
'Have I got file open?
If wbCVR Is Nothing Then
'No - open it
On Error GoTo 0
Workbooks.Open Filename:=".\CVR " & BuM & ".xls", UpdateLinks:=0,
ReadOnly:=True, Notify:=False
Set wsd = ActiveWorkbook.Worksheets("Detail Sheet")
Set wss = ActiveWorkbook.Worksheets("Summary")
Else
wbCVR.Activate
'Yes - use it
Set wsd = wbCVR.Worksheets("Detail Sheet")
Set wss = wbCVR.Worksheets("Summary")
End If
Application.DisplayAlerts = False
With wb
.Worksheets.Add befo=.Worksheets("Pivot Table")
With .ActiveSheet
.name = BuM
.Tab.ColorIndex = sc
wsd.Cells.Copy .Cells
End With
wss.Range("CopyOut").Copy
.Worksheets("Summary by BUM's").Cells(5, x).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats 'THIS LINE WORKS Data in the right place
Application.CutCopyMode = False
.Worksheets(BuM).Select 'PROBLEM HERE! SELECT METHOD FAILED
Cells.Select
Selection.EntireRow.Hidden = False
End With

Start with summary workbok open, delete the old data sheet, insert new sheet
rename it, open another workbook and copy data from it, paste into worksheet
in first workbook, then swap to recentlycreated worksheet, now it fails, Why?

Nested With's
 

No, I that's a mistake here, I was trying to delete unnecessary code, then
realised I'd cut too much out, I copied the dim, but then forgot to change
wss to wsd.
thanks for a quick response

"barnabel" wrote:

quick guess,
you dim wss twice and don't dim wsd. Could this be the problem?

"Graham Y" wrote:

I'm having problems with this code (I've include more than I need to just to
show it is all initialised properly)...
Dim wb As Workbook
Dim wss As Worksheet, wss As Worksheet
Set wb = ActiveWorkbook
Application.DisplayAlerts = False
If SheetExists(BuM) Then
Sheets(BuM).Delete
End If
Application.DisplayAlerts = True
On Error Resume Next
Set wbCVR = Workbooks("CVR " & BuM & ".xls")
'Have I got file open?
If wbCVR Is Nothing Then
'No - open it
On Error GoTo 0
Workbooks.Open Filename:=".\CVR " & BuM & ".xls", UpdateLinks:=0,
ReadOnly:=True, Notify:=False
Set wsd = ActiveWorkbook.Worksheets("Detail Sheet")
Set wss = ActiveWorkbook.Worksheets("Summary")
Else
wbCVR.Activate
'Yes - use it
Set wsd = wbCVR.Worksheets("Detail Sheet")
Set wss = wbCVR.Worksheets("Summary")
End If
Application.DisplayAlerts = False
With wb
.Worksheets.Add befo=.Worksheets("Pivot Table")
With .ActiveSheet
.name = BuM
.Tab.ColorIndex = sc
wsd.Cells.Copy .Cells
End With
wss.Range("CopyOut").Copy
.Worksheets("Summary by BUM's").Cells(5, x).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats 'THIS LINE WORKS Data in the right place
Application.CutCopyMode = False
.Worksheets(BuM).Select 'PROBLEM HERE! SELECT METHOD FAILED
Cells.Select
Selection.EntireRow.Hidden = False
End With

Start with summary workbok open, delete the old data sheet, insert new sheet
rename it, open another workbook and copy data from it, paste into worksheet
in first workbook, then swap to recentlycreated worksheet, now it fails, Why?