Error VBA help: copy specific cells from multiple workbooks in a folder to specific locations in master worksheet

[michelernqm]

I am new to VBA and I am getting hung up on trying to pull specific cells from multiple files into a master sheet. Example: I have a folder "C:\Users\me\main folder" that stores all the files needing referenced to "master" workbook. Each file in the "main folder" has the same layout with 4 tabs each. I need specific cells from each file, "Sheet 1" and the data carried over and written to the "master" workbook. I need "B22" cell from "Sheet 1" (from all workbooks in the "main folder") to be written in the master file cell "A1" while looping through the folder.



Sub ExtractCells()
Dim wb As Workbook
Dim ws As Worksheet
Dim MySheet As String
Dim r1 As Range
Dim i As Integer
Dim OpenWorkbook As Workbook
Dim OpenWorksheet As Worksheet
Dim SheetName As String
Dim Directory As String
Dim FileSpec As String
Dim MyFile As String

Directory = "C:\Users\me\main folder"
FileSpec = ".xlsx"
MyFile = Dir(Directory & "*" & FileSpec)
SheetName = "Sheet 1"
Set wb = ThisWorkbook
MySheet = "master"
Set ws = wb.Worksheets(MySheet)
Set r1 = ws.Range("A1")
i = 0
Do While MyFile <> ""
Set OpenWorkbook = Application.Workbooks.Open(Filename:=Directory & MyFile, ReadOnly:=True)
Set OpenWorksheet = OpenWorkbook.Worksheets(SheetName)
With OpenWorksheet
r1.Offset(i, 0).Value = .Range("B22").Value
End With
i = i + 1
MyFile = Dir
Loop
End Sub

 

[DanteAmor]

Hi and welcome to MrExcel

What problem do you have with your macro or what do you need to add to your macro?

 

[michelernqm]

I keep getting an error on line : Set ws = wb.Worksheets(MySheet). I have used this before but it's been a while, not sure what I am doing wrong

 

[DanteAmor]

what does the error message say? Do you have a sheet called "master" in your book? did you erase it or did you change its name?

 

[michelernqm]

Identifier under cursor is not recognized

 

[michelernqm]

the sheet is called "master" yes

 

[DanteAmor]

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.