Nested With's

G

Graham Y

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 before:=.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?
 
G

Graham Y

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top