New Guy who needs VBA code for big project

[lyokoboy]

I'm have no solid experience in writing macro, but I can record a simple one. I need a macro code that allows a master workbook to lookup multiple workbooks (no defined amount), copy certain cells in different sheets, and paste them into the masterbook in certain areas. I'm asking for helping knowing this will take time and brain power.

To assist, I need to copy 67 cells from multiple workbooks 20 in sheet1, the 47 in sheet 2, and copy them into a masterbook sheet 1 separated by row for each workbook the macro looks up.

 

[seguin85]

Are all these workbooks located in the same folder? Is there any other workbooks in that folder that do not need to be copied over to the master workbook?

 

[lyokoboy]

Assume that all workbooks in the target folder have specific cells that need to be copied into separate rows of the master.

 

[seguin85]

Are these specific cells the same between each workbook? If not, how do you determine what cells are to be copied to the main file?

 

[lyokoboy]

They will be the same cells being searched in the multiple workbooks. The workbooks are forms which individuals fill out and are stored in the folder being searched. The intended macro then searches for their answers and pastes them in individual rows in the master.

Are these specific cells the same between each workbook? If not, how do you determine what cells are to be copied to the main file?

 

[seguin85]

Do you have a list of what cells will be copied? And then all these cells from workbook1 will be in row 1, all the cells from workbook to will be in row3, etc?

 

[lyokoboy]

Yes! Sorry for the delayed response. Cells that will be copied are as follows- Sheet 1: B2,B3,B4,B5,B6,B7,E2,E3,E4,E5,E6,E7,B10,B11,B12,B13,B14,B15,D12,A19.
Sheet 2: B6,B8,B10,B12,B14,B16,B18,B20,B22,B24,E6,E8,E10,E12,E14,E16,E18,E20,E22,H6,H8,H10,H12,H14,H16,H18,H20,K6,K8,K10,K12,K14,K16,K18,B28,B30,B32,B34,B36,H24,H26,H28,H30,H34,K22,K24,K26

 

[lyokoboy]

@Sequin85, can you help me? Is there anyone who can help me with this?

 

[seguin85]

@Sequin85, can you help me? Is there anyone who can help me with this?

Here is a code that should work, you may have to move some lines of code around if you want your data in a particular order. All your forms that are should be copied over should be located in the target folder and in that folder there should not be any other excel files.

Hope you can make this work for you

Sub CopyData()

Dim NewForm As Workbook, Master As Workbook
Set Master = ActiveWorkbook
Dim x As Integer
x = 2
Application.ScreenUpdating = False


'Set TargetFolderHere
MyPath = "C:\Desktop\TargetFolder\"
MyFile = Dir(MyPath & "*.xl*", vbNormal)


'Loops to open files
Do While MyFile <> ""
    
    Workbooks.Open MyPath & MyFile
    Set NewForm = ActiveWorkbook
    Master.Activate
    
    NewForm.Sheets("Sheet1").Range("B2:B7").Copy
    Master.Sheets("Sheet1").Cells(x, 1).PasteSpecial Transpose:=True
    
    NewForm.Sheets("Sheet1").Range("E2:E7").Copy
    Master.Sheets("Sheet1").Cells(x, 7).PasteSpecial Transpose:=True
    
    NewForm.Sheets("Sheet1").Range("B10:B15").Copy
    Master.Sheets("Sheet1").Cells(x, 13).PasteSpecial Transpose:=True
    
    NewForm.Sheets("Sheet1").Range("D12").Copy
    Master.Sheets("Sheet1").Cells(x, 19).PasteSpecial Transpose:=True
    
    NewForm.Sheets("Sheet1").Range("A19").Copy
    Master.Sheets("Sheet1").Cells(x, 20).PasteSpecial Transpose:=True
    
    NewForm.Sheets("Sheet2").Range("B6,B8,B10,B12,B14,B16,B18,B20,B22,B24").Copy
    Master.Sheets("Sheet1").Cells(x, 21).PasteSpecial Transpose:=True
    
    NewForm.Sheets("Sheet2").Range("E6,E8,E10,E12,E14,E16,E18,E20,E22").Copy
    Master.Sheets("Sheet1").Cells(x, 31).PasteSpecial Transpose:=True
    
    NewForm.Sheets("Sheet2").Range("H6,H8,H10,H12,H14,H16,H18,H20").Copy
    Master.Sheets("Sheet1").Cells(x, 40).PasteSpecial Transpose:=True
    
    NewForm.Sheets("Sheet2").Range("K6,K8,K10,K12,K14,K16,K18").Copy
    Master.Sheets("Sheet1").Cells(x, 48).PasteSpecial Transpose:=True
    
    NewForm.Sheets("Sheet2").Range("B28,B30,B32,B34,B36").Copy
    Master.Sheets("Sheet1").Cells(x, 55).PasteSpecial Transpose:=True
    
    NewForm.Sheets("Sheet2").Range("H24,H26,H28,H30,H34").Copy
    Master.Sheets("Sheet1").Cells(x, 60).PasteSpecial Transpose:=True
    
    NewForm.Sheets("Sheet2").Range("K22,K24,K26").Copy
    Master.Sheets("Sheet1").Cells(x, 65).PasteSpecial Transpose:=True
    
    'Closes File
    NewForm.Close
            
    'Used to skip a line
    x = x + 2
    MyFile = Dir
Loop


Application.ScreenUpdating = True


End Sub

 

[lyokoboy]

Thank you very much for getting me this far!!!!

I replaced MyPath with the folder location and renamed the sheet names in the code where the cells are being copied from. Those were the only edits and I received and I received error msg: Invalid outside procedure.