Results 1 to 10 of 10

vba to close a workbook

Threaded View

ALPHAKENNYBODY vba to close a workbook 01-22-2015, 07:22 AM
Richard Buttrey Re: vba to close a workbook 01-22-2015, 07:26 AM
ALPHAKENNYBODY Re: vba to close a workbook 01-22-2015, 07:31 AM
Norie Re: vba to close a workbook 01-22-2015, 07:43 AM
ALPHAKENNYBODY Re: vba to close a workbook 01-22-2015, 11:42 AM
Norie Re: vba to close a workbook 01-22-2015, 12:02 PM
ALPHAKENNYBODY Re: vba to close a workbook 01-22-2015, 12:56 PM
Norie Re: vba to close a workbook 01-22-2015, 12:57 PM
ALPHAKENNYBODY Re: vba to close a workbook 01-22-2015, 12:58 PM
Norie Re: vba to close a workbook 01-22-2015, 01:07 PM
  1. #1
    Registered User
    Join Date
    10-22-2014
    Location
    england
    MS-Off Ver
    2007
    Posts
    9

    vba to close a workbook

    Hi,

    I have a macro to copy a range of cells from one workbook to another. It starts by checking if the source workbook is open and either opening or activating accordingly.

    At the end of the Macro I would like it to close the source workbook if it wasn't already open at the start. Is that possible?

    This is the macro:

    Sub AE()
    '
    ' AE Macro
    '
    Dim wkbSource As Workbook
    
        IsWorkBookOpen = ("C:\Users\david\Desktop\book1.xlsx")
    
        If Ret = True Then
        wkbSource.Activate
        Else
        Set wkbSource = Workbooks.Open("C:\Users\david\Desktop\book1.xlsx")
        End If
        
    Dim ws1 As Worksheet:   Set ws1 = wkbSource.Sheets("SECTION 100")
    Dim ws2 As Worksheet:   Set ws2 = ThisWorkbook.Sheets("Costing form")
    Dim rng1 As Range, rng2 As Range
    Set rng1 = ws1.Range("A_and_E")
    Set rng2 = ws2.Range("INSERT_LINE_HERE")
    rng1.Select
    rng1rows = Selection.Rows.Count
    
    ThisWorkbook.Activate
    rng2.Resize(rng1rows).EntireRow.Insert Shift:=xlDown
    wkbSource.Activate
    rng1.Copy
    ThisWorkbook.Activate
    rng2.Offset(-rng1rows).Select
     Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
    Range("last_three_columns").Copy
    rng2.Offset(-rng1.Rows.Count, 13).Select
    Dim numrows As Long, numcolumns As Integer
    numrows = Selection.Rows.Count
    numcolumns = Selection.Columns.Count
    Selection.Resize(numrows + (rng1.Rows.Count) - 1, numcolumns + 2).Select
     Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    rng2.Offset(-rng1.Rows.Count, -3).Select
    numrows = Selection.Rows.Count
    numcolumns = Selection.Columns.Count
    Selection.Resize(numrows, numcolumns + 18).Select
    With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = 3
            .ThemeColor = xlThemeColorLight1
            .TintAndShade = 0
            .PatternTintAndShade = 0
    End With
        With Selection.Font
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
    End With
    Selection.Font.Bold = True
    
    Range("A21").Select
    
    End Sub
    Last edited by ALPHAKENNYBODY; 01-22-2015 at 07:31 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Copy from first workbook, close open new workbook paste close
    By johncarr1952 in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 08-04-2021, 08:50 AM
  2. vba code to disable only close button and close through a button in Excel workbook
    By atif_ar in forum Excel Programming / VBA / Macros
    Replies: 47
    Last Post: 11-23-2014, 02:41 PM
  3. [SOLVED] Code to cut cell from one workbook, close same workbook, and paste in different workbook
    By kosherboy in forum Excel Programming / VBA / Macros
    Replies: 30
    Last Post: 03-13-2014, 04:01 PM
  4. Disable close workbook but still force users to close thru command button
    By rathig in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-18-2013, 07:29 AM
  5. [SOLVED] Edit code to close all workbooks except active workbook and other specific workbook
    By rocksan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-04-2012, 09:29 PM

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