Results 1 to 11 of 11

Object Variable Or With Block Not Set

Threaded View

  1. #1
    Registered User
    Join Date
    10-24-2010
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    49

    Object Variable Or With Block Not Set

    Originally and after some help I had 2 separate pieces of code, the first to copy the active worksheet into each workbook in a folder and the second to delete a named worksheet if it existed.

    To make the code more efficient and use techniques I liked from both pieces of code, I have tried to merge them, the result being the code below.

    However, I get the message Object Variable Or With Block Not Set.

    What have I done wrong?

    Public Sub Alex()

    Const strFldrPath As String = "C:\Data\Communications Division\Testing Folder\Working Time Master\Working Time Records\"

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim Filecell As Range

    Application.ScreenUpdating = False

    Set ws = ActiveWorkbook.ActiveSheet

    For Each Filecell In Intersect(ActiveWorkbook.Sheets("Sheet1").UsedRange, ActiveWorkbook.Sheets("Sheet1").[A:A])
    If ActiveWorkbook.ReadOnly = True Then
    ThisWorkbook.Sheets("Sheet2").Cells(ThisWorkbook.Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row + 1, 1).Value = ActiveWorkbook.Name
    ActiveWorkbook.Close False
    ElseIf Dir(strFldrPath & Filecell.Text) <> vbNullString Then
    Set wb = Workbooks.Open(Filename:=strFldrPath & Filecell.Text)
    ws.Copy After:=wb.Sheets(wb.Sheets.Count)

    For Each ws In ActiveWorkbook.Worksheets
    If ws.Name = "20 DEC 10 - 20 MAR 11" Then
    Application.DisplayAlerts = False
    ws.Delete
    On Error Resume Next
    End If

    Next ws

    End If

    ActiveWorkbook.Close True

    Next Filecell

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    End Sub
    Last edited by AlexRoberts; 10-28-2011 at 07:35 AM. Reason: Solved

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1