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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,623
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,623
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,623
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,130,256
Messages
5,641,144
Members
417,195
Latest member
Vishal kumar

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