+ Reply to Thread
Results 1 to 1 of 1

Switching between workbooks

Hybrid View

  1. #1
    Registered User
    Join Date
    08-08-2010
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2010
    Posts
    85

    Unhappy Switching between workbooks

    Hi
    I have a parent workbook that has a dynamic name, so I used one word in the file name that was constant to set the workbook.

    Next I am taking 2 tabs from this workbook and creating new workbooks from them. My problem is I cannot seem to flip back and forth between the parent and the new workbooks.

    I get error message at the first step that says "runtime 1004 Application defined or object defined error"

    I'm sure its a simple fix, but I've tried so many things, I can't get it!

    Sub CreateUWReport_FollowupReport()
    '
    ' CreateUWReport_FollowupReport Macro
    ' Macro recorded 08/08/2010
    '
    
    'selects UW report sheet, copy into new book
        
    Dim wb As Workbook
    Dim wbchecklist As Workbook
    
    For Each wb In Workbooks
        If InStr(1, LCase(wb.Name), "checklist") > 0 Then
            Set wbchecklist = wb
        End If
    Next wb
    
        
        With wbchecklist
        .Activate
        
       ActiveSheet.Range("UW Report - FYI ONLY").Select
       ActiveSheet.Range("UW Report - FYI ONLY").Copy
       'copys all cells/paste special values
        Cells.Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("A1").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = ""
        'select autofilter to get rid of unwanted lines, then turns off autofilter
        Rows("16:16").Select
        Selection.AutoFilter
        Selection.AutoFilter Field:=1, Criteria1:="="
        Rows("20:139").Select
        Selection.Delete Shift:=xlUp
        Selection.AutoFilter Field:=1, Criteria1:="1"
        Rows("16:16").Select
        Selection.AutoFilter
        'selects rows for autofit
        Rows("16:130").Select
        Selection.Rows.AutoFit
        Range("C12").Select
    
        
        'saves this new workbook with preset file format
        ActiveWorkbook.SaveAs "UW Report" & " " & Sheet1.Range("a1").Value & ".xls", FileFormat:=xlNormal
    
        End With
        
        'flips back to checklist to do next report
            
        With wbchecklist
        .Activate
        
        ActiveSheet.Range("Follow-Up Tab - PRINT AND FILE").Select
        ActiveSheet.Range("Follow-Up Tab - PRINT AND FILE").Copy
        'copys all cells/paste special values
        Cells.Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Rows("9:9").Select
        Application.CutCopyMode = False
        
        'select autofilter to get rid of unwanted lines, then turns off autofilter
        Selection.AutoFilter
        Selection.AutoFilter Field:=1, Criteria1:="0"
        Rows("12:190").Select
        ActiveWindow.SmallScroll Down:=-6
        Rows("12:129").Select
        Selection.Delete Shift:=xlUp
        Selection.AutoFilter Field:=1, Criteria1:="1"
        Rows("9:9").Select
        Selection.AutoFilter
        
        'selects rows for autofit
        Rows("10:46").Select
        Selection.Rows.AutoFit
        Range("A1").Select
        
        'saves the workbook with preset file format
        ActiveWorkbook.SaveAs "Fwup Report" & " " & Sheet1.Range("a1").Value & ".xls", FileFormat:=xlNormal
        
      End With
      
        'figure out how to go back to the original file, but just have it look for name 'checklist' in name
        With wbchecklist
        .Activate
        End With
        
    End Sub
    Last edited by romperstomper; 08-09-2010 at 09:15 AM. Reason: add closing code tag

+ Reply to Thread

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