Hi all, I found some code to copy the worksheet from one workbook.
Currently I am able to copy the worksheet using hardcoded path from cell value,D3 and filename from cell value, D4.
However there is more than one worksheet to copy over in the directory.
I would like to program in a way that it will locate all the files with the filename contain cell value,D4 and do the copy of worksheet over, into a newly created worksheet named with the cell value, A1.
For example, the cell value,D4 is 06032009. In the directory, there are 10 files with 3 files contain the cell value (06032009_prod1.xls, 06032009_prod2.xls, 06032009_prod3.xls).
The macro will then create 3 new tabs with the content being copied over.
Currently I am able to copy the worksheet using hardcoded path from cell value,D3 and filename from cell value, D4.
However there is more than one worksheet to copy over in the directory.
I would like to program in a way that it will locate all the files with the filename contain cell value,D4 and do the copy of worksheet over, into a newly created worksheet named with the cell value, A1.
For example, the cell value,D4 is 06032009. In the directory, there are 10 files with 3 files contain the cell value (06032009_prod1.xls, 06032009_prod2.xls, 06032009_prod3.xls).
The macro will then create 3 new tabs with the content being copied over.
Code:
Sub CopyWorksheet()
Sheets("Menu").Select
PathName = Range("D3").Value
FileName = Range("D4").Value
ControlFile = ActiveWorkbook.Name
Workbooks.Open FileName:=PathName & FileName
TabName = Range("A1").Value
ActiveSheet.Name = TabName
Sheets(TabName).Copy After:=Workbooks(ControlFile).Sheets(1)
Windows(FileName).Activate
ActiveWorkbook.Close SaveChanges:=False
Windows(ControlFile).Activate
Sheets("Menu").Select
Range("D8").Select
ActiveCell.Value = "Completed"
Range("D9").Select
End Sub