VBA copy range in another sheet to current sheet

[APML]

Hi All. Let me start by saying that I'm not very good at VBA, so need a little help.

I have 2 workbooks, one is called Journal.xlsx and the other is called abcd.xlsm.
I've managed to come up with the following code that copies ranges from Jornal workbook to abcd workbook then close abcd workbook. ..... but for some reason, the copied range in abcd is still selected, what code can I add it so it's no longer selected, for the sake of clarity what I'm saying is once the macro has run I'm finding that the worksheet "engine" is still selected ... this part of the code
Workbooks("Journal.xlsm").Worksheets("engine").Range("A1").PasteSpecial Paste:=xlPasteValues
Is there a way to unselect it during macro operation? Below is the whole code

Sub Copy_PasteSpecial_Method()

'Copy range to clipboard
Workbooks("abcd.xlsx").Worksheets("Sheet1").Range("A1:IZ2121").Copy
'PasteSpecial to paste values, formulas, formats, etc.
Workbooks("Journal.xlsm").Worksheets("engine").Range("A1").PasteSpecial Paste:=xlPasteValues

'Copy range to clipboard
Workbooks("abcd.xlsx").Worksheets("Sheet2").Range("A1:AXA2110").Copy
'PasteSpecial to paste values, formulas, formats, etc.
Workbooks("Journal.xlsm").Worksheets("ASX_Data").Range("I12").PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False
Workbooks("abcd.xlsx").Close SaveChanges:=False
Range("A1").Select
End Sub

 

[APML]

One more thing..
How or where do I add the following, it a message that I want people to see after the data has been copied from abcd workbook

MsgBox ("Data has been Updated and ASX.xlsm has been Closed. Pls delete (or rename) ASX.xlsm (Todays Data File) to prevent an issue occuring on next Update")

Actually, I've worked that bit out. Again thanks for your help.

 

[APML]

Sure. You need this extra function for that though.

Sub Copy_PasteSpecial_Method()

'First check if needed workbooks are opened.  If not, notify the user and exit.
If Workbook_Is_Opened("abcd.xlsx") = False Then
    MsgBox "Please open [ABCD] for this operation to occur.", vbCritical, "Try Again After Opening Workbook"
    Exit Sub
End If

If Workbook_Is_Opened("Journal.xlsm") = False Then
    MsgBox "Please open [Journal] for this operation to occur.", vbCritical, "Try Again After Opening Workbook"
    Exit Sub
End If

'Unlike other variable types in VBA, ranges need to be "Set".
'So unlike if we assign x = 8, we have to Set rangeName = Range("A1"), for example.

Dim copyRange As Range
Set copyRange = Workbooks("abcd.xlsx").Worksheets("Sheet1").Range("A1:IZ2121")

Dim pasteRange As Range
Set pasteRange = Workbooks("Journal.xlsm").Worksheets("engine").Range("A1:IZ2121")

'Turn off calculations will assigning values to cells to save a lot of time!
Application.Calculation = xlCalculationManual

    '.PasteSpecial Paste:=xlPasteValues is just the values, so we can do
    'And here, we are assigning the value (a property of the range, not the range itself . . . hence just the = and not Set =).
    pasteRange.Value = copyRange.Value


'But your second copy/paste is a little different.
'The table you are copying starts in cell A1, but you want it to paste in Cell I12 of the other sheet.
'The copy range is the same as before:
Set copyRange = Workbooks("abcd.xlsx").Worksheets("Sheet2").Range("A1:AXA2110")

'But we have to "resize" the PASTE range to be the same size as the copy range.
Set pasteRange = Workbooks("Journal.xlsm").Worksheets("ASX_Data").Range("I12").Resize(copyRange.Rows.Count, copyRange.Columns.Count)

    'Again, .PasteSpecial Paste:=xlPasteValues is just the values, so we can do
    pasteRange.Value = copyRange.Value

'Turn back on formula calculations (so that formulas will work as normal).
Application.Calculation = xlCalculationAutomatic

Workbooks("abcd.xlsx").Close SaveChanges:=False
Range("A1").Select

End Sub

Function Workbook_Is_Opened(workbookName As String)
Workbook_Is_Opened = False
Dim wb As Workbook
For Each wb In Application.Workbooks
    If wb.Name = workbookName Then
        Workbook_Is_Opened = True
        Exit Function
    End If
Next wb
End Function

Sure. You need this extra function for that though.

Sub Copy_PasteSpecial_Method()

'First check if needed workbooks are opened.  If not, notify the user and exit.
If Workbook_Is_Opened("abcd.xlsx") = False Then
    MsgBox "Please open [ABCD] for this operation to occur.", vbCritical, "Try Again After Opening Workbook"
    Exit Sub
End If

If Workbook_Is_Opened("Journal.xlsm") = False Then
    MsgBox "Please open [Journal] for this operation to occur.", vbCritical, "Try Again After Opening Workbook"
    Exit Sub
End If

'Unlike other variable types in VBA, ranges need to be "Set".
'So unlike if we assign x = 8, we have to Set rangeName = Range("A1"), for example.

Dim copyRange As Range
Set copyRange = Workbooks("abcd.xlsx").Worksheets("Sheet1").Range("A1:IZ2121")

Dim pasteRange As Range
Set pasteRange = Workbooks("Journal.xlsm").Worksheets("engine").Range("A1:IZ2121")

'Turn off calculations will assigning values to cells to save a lot of time!
Application.Calculation = xlCalculationManual

    '.PasteSpecial Paste:=xlPasteValues is just the values, so we can do
    'And here, we are assigning the value (a property of the range, not the range itself . . . hence just the = and not Set =).
    pasteRange.Value = copyRange.Value


'But your second copy/paste is a little different.
'The table you are copying starts in cell A1, but you want it to paste in Cell I12 of the other sheet.
'The copy range is the same as before:
Set copyRange = Workbooks("abcd.xlsx").Worksheets("Sheet2").Range("A1:AXA2110")

'But we have to "resize" the PASTE range to be the same size as the copy range.
Set pasteRange = Workbooks("Journal.xlsm").Worksheets("ASX_Data").Range("I12").Resize(copyRange.Rows.Count, copyRange.Columns.Count)

    'Again, .PasteSpecial Paste:=xlPasteValues is just the values, so we can do
    pasteRange.Value = copyRange.Value

'Turn back on formula calculations (so that formulas will work as normal).
Application.Calculation = xlCalculationAutomatic

Workbooks("abcd.xlsx").Close SaveChanges:=False
Range("A1").Select

End Sub

Function Workbook_Is_Opened(workbookName As String)
Workbook_Is_Opened = False
Dim wb As Workbook
For Each wb In Application.Workbooks
    If wb.Name = workbookName Then
        Workbook_Is_Opened = True
        Exit Function
    End If
Next wb
End Function

Hi, we have been using your VBA for a few weeks and it works brilliantly. I want to make 1 change and was hoping you'd be able to help me.
The file name we copy and paste the ranges from (which is abcd.xlsx) was getting a little confusing for some of our people so now we add a date to the end of the file name. i.e abcd_20221015.xlsx the day before would be abcd_20221014 etc
Is it possible to allow for this (maybe adding a wildcard *) to the code? I've copied the code below:

Sub Copy_PasteSpecial_Method()

'First check if needed workbooks are opened. If not, notify the user and exit.
If Workbook_Is_Opened("abcd.xlsx") = False Then
MsgBox "Please open File ASX.xlsm for this operation to occur.", vbCritical, "Try Again After Opening Workbook"
Exit Sub
End If

If Workbook_Is_Opened("Journal_7111_GG.xlsm") = False Then
MsgBox "Please open [Journal_7111_GG] for this operation to occur.", vbCritical, "Try Again After Opening Workbook"
Exit Sub
End If

'Unlike other variable types in VBA, ranges need to be "Set".
'So unlike if we assign x = 8, we have to Set rangeName = Range("A1"), for example.

Dim copyRange As Range
Set copyRange = Workbooks("abcd.xlsx").Worksheets("Sheet1").Range("A1:IZ2121")

Dim pasteRange As Range
Set pasteRange = Workbooks("Journal_7111_GG.xlsm").Worksheets("engine").Range("A1:IZ2121")

'Turn off calculations will assigning values to cells to save a lot of time!
Application.Calculation = xlCalculationManual

'.PasteSpecial Paste:=xlPasteValues is just the values, so we can do
'And here, we are assigning the value (a property of the range, not the range itself . . . hence just the = and not Set =).
pasteRange.Value = copyRange.Value


'But your second copy/paste is a little different.
'The table you are copying starts in cell A1, but you want it to paste in Cell I12 of the other sheet.
'The copy range is the same as before:
Set copyRange = Workbooks("abcd.xlsx").Worksheets("Sheet2").Range("A1:AXG2110")

'But we have to "resize" the PASTE range to be the same size as the copy range.
Set pasteRange = Workbooks("Journal_7111_GG.xlsm").Worksheets("ASX_Data").Range("H12").Resize(copyRange.Rows.Count, copyRange.Columns.Count)

'Again, .PasteSpecial Paste:=xlPasteValues is just the values, so we can do
pasteRange.Value = copyRange.Value

'Turn back on formula calculations (so that formulas will work as normal).
Application.Calculation = xlCalculationAutomatic

Workbooks("abcd.xlsx").Close SaveChanges:=False
Range("A1").Select
MsgBox ("Data has been Updated and ASX.xlsm has been Closed." & vbCrLf & vbCrLf & "Pls delete (or rename)it." & vbCrLf & vbCrLf & "If you don't delete (or rename), it may cause a problem with tomorrows download")

End Sub

Function Workbook_Is_Opened(workbookName As String)
Workbook_Is_Opened = False
Dim wb As Workbook
For Each wb In Application.Workbooks
If wb.Name = workbookName Then
Workbook_Is_Opened = True
Exit Function
End If
Next wb

End Function