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]

Which line did it break at?

 

[lyokoboy]

Line 2 of the code, "Set" is highlighted.

Which line did it break at?

 

[seguin85]

Can you post your code?

 

[lyokoboy]

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

MyPath = "(for privacy purposes, I removed the file path pasted from windows explorer"
MyFile = Dir(MyPath & "*.xl*", vbNormal)

Do While MyFile <> ""

Workbooks.Open MyPath & MyFile
Set NewForm = ActiveWorkbook
Master.Activate

NewForm.Sheets("WorkPlace").Range("B2:B7").Copy
Master.Sheets("Sheet1").Cells(x, 1).PasteSpecial Transpose:=True

NewForm.Sheets("WorkPlace").Range("E2:E7").Copy
Master.Sheets("Sheet1").Cells(x, 7).PasteSpecial Transpose:=True

NewForm.Sheets("WorkPlace").Range("B10:B15").Copy
Master.Sheets("Sheet1").Cells(x, 13).PasteSpecial Transpose:=True

NewForm.Sheets("WorkPlace").Range("D12").Copy
Master.Sheets("Sheet1").Cells(x, 19).PasteSpecial Transpose:=True

NewForm.Sheets("WorkPlace").Range("A19").Copy
Master.Sheets("Sheet1").Cells(x, 20).PasteSpecial Transpose:=True

NewForm.Sheets("APPLICATIONS").Range("B6,B8,B10,B12,B14,B16,B18,B20,B22,B24").Copy
Master.Sheets("Sheet1").Cells(x, 21).PasteSpecial Transpose:=True

NewForm.Sheets("APPLICATIONS").Range("E6,E8,E10,E12,E14,E16,E18,E20,E22").Copy
Master.Sheets("Sheet1").Cells(x, 31).PasteSpecial Transpose:=True

NewForm.Sheets("APPLICATIONS").Range("H6,H8,H10,H12,H14,H16,H18,H20").Copy
Master.Sheets("Sheet1").Cells(x, 40).PasteSpecial Transpose:=True

NewForm.Sheets("APPLICATIONS").Range("K6,K8,K10,K12,K14,K16,K18").Copy
Master.Sheets("Sheet1").Cells(x, 48).PasteSpecial Transpose:=True

NewForm.Sheets("APPLICATIONS").Range("B28,B30,B32,B34,B36").Copy
Master.Sheets("Sheet1").Cells(x, 55).PasteSpecial Transpose:=True

NewForm.Sheets("APPLICATIONS").Range("H24,H26,H28,H30,H34").Copy
Master.Sheets("Sheet1").Cells(x, 60).PasteSpecial Transpose:=True

NewForm.Sheets("APPLICATIONS").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

Can you post your code?

 

[MARK858]

Where are your Sub and End Sub lines?

 

[lyokoboy]

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

MyPath = "C:\Users\tc.sample\OneDrive - Coretek Services\Projects\SharePoint\GDLS Production Deploy - Documents\Deployment\Deployment Forms"
MyFile = Dir(MyPath & "*.xl*", vbNormal)
 
Do While MyFile <> ""
    
    Workbooks.Open MyPath & MyFile
    Set NewForm = ActiveWorkbook
    Master.Activate
    
    NewForm.Sheets("GDLS WorkPlace").Range("B2:B7").Copy
    Master.Sheets("Sheet1").Cells(x, 1).PasteSpecial Transpose:=True
    
    NewForm.Sheets("GDLS WorkPlace").Range("E2:E7").Copy
    Master.Sheets("Sheet1").Cells(x, 7).PasteSpecial Transpose:=True
    
    NewForm.Sheets("GDLS WorkPlace").Range("B10:B15").Copy
    Master.Sheets("Sheet1").Cells(x, 13).PasteSpecial Transpose:=True
    
    NewForm.Sheets("GDLS WorkPlace").Range("D12").Copy
    Master.Sheets("Sheet1").Cells(x, 19).PasteSpecial Transpose:=True
    
    NewForm.Sheets("GDLS WorkPlace").Range("A19").Copy
    Master.Sheets("Sheet1").Cells(x, 20).PasteSpecial Transpose:=True
    
    NewForm.Sheets("APPLICATIONS").Range("B6,B8,B10,B12,B14,B16,B18,B20,B22,B24").Copy
    Master.Sheets("Sheet1").Cells(x, 21).PasteSpecial Transpose:=True
    
    NewForm.Sheets("APPLICATIONS").Range("E6,E8,E10,E12,E14,E16,E18,E20,E22").Copy
    Master.Sheets("Sheet1").Cells(x, 31).PasteSpecial Transpose:=True
    
    NewForm.Sheets("APPLICATIONS").Range("H6,H8,H10,H12,H14,H16,H18,H20").Copy
    Master.Sheets("Sheet1").Cells(x, 40).PasteSpecial Transpose:=True
    
    NewForm.Sheets("APPLICATIONS").Range("K6,K8,K10,K12,K14,K16,K18").Copy
    Master.Sheets("Sheet1").Cells(x, 48).PasteSpecial Transpose:=True
    
    NewForm.Sheets("APPLICATIONS").Range("B28,B30,B32,B34,B36").Copy
    Master.Sheets("Sheet1").Cells(x, 55).PasteSpecial Transpose:=True
    
    NewForm.Sheets("APPLICATIONS").Range("H24,H26,H28,H30,H34").Copy
    Master.Sheets("Sheet1").Cells(x, 60).PasteSpecial Transpose:=True
    
    NewForm.Sheets("APPLICATIONS").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
End Sub
 
[

 

[seguin85]

Ensure that the macro is in your master work book and place a back slash after forms.

- Documents\Deployment\Deployment Forms"

See how that goes