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

michelernqm

New Member
Joined
Jun 19, 2020
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Hi and welcome to MrExcel

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

michelernqm

New Member
Joined
Jun 19, 2020
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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

New Member
Joined
Jun 19, 2020
Messages
4
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Identifier under cursor is not recognized
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,189
Messages
5,570,760
Members
412,340
Latest member
nikitesh95
Top