VBA to match the workbook name with worksheet name and copy data

[agrawaltanu21]

I have 10 workbooks in a directory, each has only one sheet. I have another main spreadsheet where I am doing all the calculations. This main spreadsheet has sheets with the same names with only an extension added at the beginning. To be more specific, let's say the workbooks in the directory are named: Analyst1.xlsx, Analyst2.xlsx and the main spreadsheet has sheets named Analyst1,Analyst2. The reason for that I have other sheets in that main workbook. I have some other macros that are calling based on the extension at the beginning.

I need to copy the information in sheet1 of Analyst1.xlsx into the sheet named Analyst1 and so on.. on the main spreadsheet if that sheet exists in the main workbook.
I have run one code but i am getting one error can you help me on this.

Sub CopytoSheet()
'
' Copy same name workbook in worksheet
'

Dim PathOfWorkbboks
Dim objFolder As Object
Dim objFile As Object
Dim Main
Dim ShtName, objName
Dim Sheetname
Dim Sheetnamee
Dim currentName As String

Main = "Cop.xlsx" '(Main Workbook File)

'Windows(Main).Activate
PathOfWorkbboks = "C:\Users\tanu_agrawal1\Downloads\Automation\d" ' Change to the path where all workbooks are
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(PathOfWorkbboks)


For x = 1 To Sheets.Count
With Sheets(x)
Sheets("XX").Activate '(I have given sheet name in XX sheet on main workbook)
Sheetname = Cells(x, 1)
Sheetnamee = Sheetname & ".xlsm"

Windows(Main).Activate
For Each objFile In objFolder.Files
objName = objFSO.Getfilename(objFile.Path)
'Sheetname = Cells(x, 1)
'Windows(Main).Activate
If objName = Sheetnamee Then
'Windows(Main).Activate
Workbooks.Open objFile
Sheets("Example user 1").Range("B7:CS16").Copy
Main = "Cop.xlsx"
'Windows(Main).Activate

'Sheets("Sheetname").Activate
ThisWorkbook.Worksheets("Sheetname").Range("b3").PasteSpecial xlPasteValues
'Windows(Main).Activate
'Range("A1").Select
'ActiveSheet.Paste
Application.CutCopyMode = False
Workbooks(objName).Close savechanges:=False

End If
Next
End With
Next x

End Sub

 

[Alex Blakenburg]

What do you want to do if the name of a workbook in the selected folder does not exist in the Main sheet ?
What are these lines meant to do ? Are we looking for whether the sheet exists or is there a list of sheet names we need to do something with ?

        Sheets("XX").Activate '(I have given sheet name in XX sheet on main workbook)
        Sheetname = Cells(x, 1)

What does this mean, show us an example of what you mean by extension

This main spreadsheet has sheets with the same names with only an extension added at the beginning.

This line appears twice but is never used.

Main = "Cop.xlsx"