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
Bookmarks