Trying to link 1 master sheet to copy 1 cell of data from 650 other excel spreadsheets

[melt80419]

Good morning,

I am trying to copy data from 1 cell over to a master spreadsheet that will pull the information from 650 other sheets. I have tried everything and now I need to figure out a VBA code. Can someone please help? I am so new to this. Thank you!

 

[mehidy1437]

What is the cell? What are the conditions to select the cell to copy?
Show some example, so someone can help.

 

[Jeffrey Mahoney]

That is a big can of worms!

I've done that before. Are you planning to do this once or many times? Are the cells you want to copy in the same place in all 650 workbooks?

I created one line of values to turn a path, workbook name, Sheet name, and cell address into a link. All you need to do is get the information for the rest of the 650 workbooks

Test link.xlsx
	A	B	C	D	E	F	G
3	Path	Workbook name	Sheet Name	Cell Address	Formula	Copied Formula into Text	Original Formula
4	C:\Users\mahonejx\Documents\	[Cummins Annual WOs.xlsx]	List	$G$52	='C:\Users\mahonejx\Documents\[Cummins Annual WOs.xlsx]List'!$G$52	PM-3107611	PM-3107611
Sheet1

Cell Formulas
Range	Formula
E4	E4	="='"&A4&B4&C4&"'!"&D4
F4:G4	F4	='C:\Users\mahonejx\Documents\[Cummins Annual WOs.xlsx]List'!$G$52

 

[melt80419]

The cell that needs to be copied is on tab named FORM N1 the cell is B9. The file path for the 650 spreadsheets are located are C:\groups\approved budgets. On the master sheet, the cell that helps find each of the 650 sheets is on B4-B658.

 

[melt80419]

I was hoping on doing it once. I tried to do indirect but I would need to have all 650 sheets open which is not feasible.

That is a big can of worms!

I've done that before. Are you planning to do this once or many times? Are the cells you want to copy in the same place in all 650 workbooks?

I created one line of values to turn a path, workbook name, Sheet name, and cell address into a link. All you need to do is get the information for the rest of the 650 workbooks

Test link.xlsx
	A	B	C	D	E	F	G
3	Path	Workbook name	Sheet Name	Cell Address	Formula	Copied Formula into Text	Original Formula
4	C:\Users\mahonejx\Documents\	[Cummins Annual WOs.xlsx]	List	$G$52	='C:\Users\mahonejx\Documents\[Cummins Annual WOs.xlsx]List'!$G$52	PM-3107611	PM-3107611
Sheet1

Cell Formulas
Range	Formula
E4	E4	="='"&A4&B4&C4&"'!"&D4
F4:G4	F4	='C:\Users\mahonejx\Documents\[Cummins Annual WOs.xlsx]List'!$G$52

 

[mehidy1437]

So, you want to copy only cell B9 from 650 workbooks of sheet named "FORM N1", and it will be paste in cell B4 to downwards on the master sheet, right?

 

[melt80419]

Close. I want to copy only cell B9 from 650 workbooks of sheet named "FORM N1", and it will be paste in cell C4 downwards. Cell B4 in the master has the name of all of the file names in the folder.

 

[melt80419]

Would I copy that formula multiple times or put that formula into the VBA as a macro?

That is a big can of worms!

I've done that before. Are you planning to do this once or many times? Are the cells you want to copy in the same place in all 650 workbooks?

I created one line of values to turn a path, workbook name, Sheet name, and cell address into a link. All you need to do is get the information for the rest of the 650 workbooks

Test link.xlsx
	A	B	C	D	E	F	G
3	Path	Workbook name	Sheet Name	Cell Address	Formula	Copied Formula into Text	Original Formula
4	C:\Users\mahonejx\Documents\	[Cummins Annual WOs.xlsx]	List	$G$52	='C:\Users\mahonejx\Documents\[Cummins Annual WOs.xlsx]List'!$G$52	PM-3107611	PM-3107611
Sheet1

Cell Formulas
Range	Formula
E4	E4	="='"&A4&B4&C4&"'!"&D4
F4:G4	F4	='C:\Users\mahonejx\Documents\[Cummins Annual WOs.xlsx]List'!$G$52

 

[mehidy1437]

See if the following works for you:

Sub LoopThroughFolder()

Dim MyFile As String, Str As String, MyDir As String, Wb As Workbook
Dim Rws As Long, Rng As Range
Set Wb = ThisWorkbook

MyDir = "  C:\groups\approved budgets\"
MyFile = Dir(MyDir & "*.xls*")
ChDir MyDir
Application.ScreenUpdating = 0
Application.DisplayAlerts = 0

Do While MyFile <> ""
Workbooks.Open (MyFile)
With Worksheets(" FORM N1")
Set Rng = Range("B9")
    If Wb.Worksheets("Sheet1").Range("c4") <> "" Then
        Rng.Copy Wb.Worksheets("Sheet1").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0)
    Else
        Rng.Copy Wb.Worksheets("Sheet1").Range("c4")
    End If
ActiveWorkbook.Close False
End With
Application.DisplayAlerts = 1
MyFile = Dir()
Loop

End Sub

 

[Jeffrey Mahoney]

You would fill in the rows of data and copy the formula down. Then you simply copy the formula over a column and paste as values. These show odd like they are a text cell. Highlight all of the newly copied values and choose DATA - TEXT TO COLUMNS. Click Delimited then Finish. All the formulas will change to results from the workbooks.

I would try to use the macro above first