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

agrawaltanu21

New Member
Joined
Aug 15, 2021
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
3,530
Office Version
  1. 365
Platform
  1. Windows
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 ?
VBA Code:
        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.
VBA Code:
Main = "Cop.xlsx"
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,477
Messages
5,831,904
Members
430,090
Latest member
bjonesh2o

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top