Need VBA to open files based on file name

[tonyjyoo]

Hello,

I need a VBA code to open a file in a specific file path, copy the set of the data, paste into another workbook then close that file.

Here's the following criteria:

File path: S:\NPH Accounting\NPH Accounting Department\Allocations\2016\Employee Allocation

- The files in this folder vary month over month (for example "01 Jan Employee Allocations 2016", "02 Feb Employee Allocations 2016", etc)

How can I code this to open the latest file and paste it into my workbook we will call "workbook A"?

Thank you in advance.

 

[tonyjyoo]

change this block of code

Month.Activate
Activesheet.cells.select
Selection.Copy
Main.Activate
Month.Sheets("Sheet5").Cells.Select
Main.Selection.Paste

I'm having trouble with this block because I don't know how to go back to the original file since we are using "active sheets" and the active sheet at that stage would be the workbook I'm opening and copying data from.

How can I set my file I'm pasting data in so I can set it as something and make it go there instead?

 

[jayesh25]

This code is working fine for me... I'll put my comments to simplify it for you.

'the below is the workbook where the data will be copied from it is getting activated
Month.Activate
'now when the above workbook is activated the data from its active sheet will be selected and copied
Activesheet.cells.select
Selection.Copy
'now the sheet where you are running this macro from is being activated
Main.Activate
'you must now select and change focus to the sheet where you want to copy your data
Main.sheets("Sheet5").select
Main.Sheets("Sheet5").Cells.Select
Main.Selection.Paste

 

[tonyjyoo]

But when you use:

'you must now select and change focus to the sheet where you want to copy your data
Main.sheets("Sheet5").select
Main.Sheets("Sheet5").Cells.Select
Main.Selection.Paste

It is in a different workbook, not within the same workbook/ So there is no "Sheet5" on the active workbook.

 

[jayesh25]

Ok.. Can you tell me what's the name of the sheet on your main workbook where you want the data to finally be pasted? And also the name of the sheet where you intend to copy this data from.

 

[tonyjyoo]

The name of the sheet on the main workbook is "Employee Allocations List" or "Sheet5".

The name of the sheet I'm copying data from is "Employee Allocations List" or "Sheet1".

Thank you so much.

 

[jayesh25]

Based on the given sheet names the below code should work fine.

The below code is assigned to open the workbook specified in Range ("A1") and refer to sheet 1 to copy the data then take it back to the main workbook and paste it on sheet5

Sub UpdateEmployeeAllocationList()


Dim Main As Workbook
Dim Month As Workbook


Set Main = Thisworkbook
file = Range("A1").value
Set Month = Workbooks.Open("S:\NPH Accounting\NPH Accounting Department\Allocations\2016\Employee Allocation\" & file & " Employee Allocations 2016.xlsx")



'the below is the workbook where the data will be copied from it is getting activated
Month.Activate
Month.sheets("Sheet1").select
'now when the above workbook is activated the data from its active sheet will be selected and copied
[FONT=Verdana]Month.sheets("Sheet1")[/FONT].cells.select
Selection.Copy
'now the sheet where you are running this macro from is being activated
Main.Activate
'you must now select and change focus to the sheet where you want to copy your data
Main.sheets("Sheet5").select
Main.Sheets("Sheet5").Cells.Select
Main.Selection.Paste
Month.Close False
End Sub

 

[tonyjyoo]

Based on the given sheet names the below code should work fine.

The below code is assigned to open the workbook specified in Range ("A1") and refer to sheet 1 to copy the data then take it back to the main workbook and paste it on sheet5

Sub UpdateEmployeeAllocationList()


Dim Main As Workbook
Dim Month As Workbook


Set Main = Thisworkbook
file = Range("A1").value
Set Month = Workbooks.Open("S:\NPH Accounting\NPH Accounting Department\Allocations\2016\Employee Allocation\" & file & " Employee Allocations 2016.xlsx")



'the below is the workbook where the data will be copied from it is getting activated
Month.Activate
Month.sheets("Sheet1").select
'now when the above workbook is activated the data from its active sheet will be selected and copied
[FONT=Verdana]Month.sheets("Sheet1")[/FONT].cells.select
Selection.Copy
'now the sheet where you are running this macro from is being activated
Main.Activate
'you must now select and change focus to the sheet where you want to copy your data
Main.sheets("Sheet5").select
Main.Sheets("Sheet5").Cells.Select
Main.Selection.Paste
Month.Close False
End Sub

This is giving me a subscript out of range error message.

 

[jayesh25]

Which line are you getting the error on? Can you de-bug this manually and check?

Try to replace this block of code

Main.Selection.Paste

[COLOR=#222222][FONT=Verdana]

with

Activesheet.paste

[/FONT][/COLOR]

 

[tonyjyoo]

It's this line of code:

Month.Sheets("Sheet1").Select

 

[tonyjyoo]

I figured it out. I changed name of sheet to match. However because of selecting so many times I think the VBA doesn't remember the original selection it copied.