Copying and pasting from a different workbook which name change

Hi Nuria,

Thank you for the forum question.

You have different options to do what you want.

One option is to use the file dialog picker or you you can create an input box but you will need to work with variables. I do not know if you have any variable knowledge but it is a way of storing information in the computer's memory.

The first code below will ask you to select the file you want to copy from and when you have selected the file the range will be copied and pasted to the destination.

Try:

Sub AnyNameYouLike()


'speed up macro code
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

'declare variables
Dim FileName As String
Dim WorkBk As Workbook


With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
If .Show = True
FileName = .SelectedItems(1)
Else
MsgBox "File not selected!", , "File selecter"
Exit Sub
End If

End With
Set WorkBk = Workbooks.Open(FileName)

Range("A2:G571").Select
Selection.Copy
Windows("Management Pack May.xlsm").Activate
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save

WorkBk.Close savechanges:=False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic

End Sub


Or you can create a an input where Excel will prompt you for a file name.

Sub AnyNameYouLike()


'speed up macro code
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

'declare variables
Dim FileName As String
Dim WorkBk As Workbook

FileName=InputBox("Please enter the file name")


Set WorkBk = Workbooks.Open("O:\Treasury\RiskCtrl\Risk Reporting\SRA\Trader Sign Off\Explainers 2016\" & FileName)

Range("A2:G571").Select
Selection.Copy
Windows("Management Pack May.xlsm").Activate
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save

WorkBk.Close savechanges:=False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic

End Sub

Please test the code in copies of your workbooks and let me know if it is working.

If you want Excel automatically to know the name of the next workbook it is much more complicated.


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector