Importing a Module

[jorgan202]

Hi please can someone help me, I have spent ages trying to write the code, testing as I go along, not very good at it. When I import a module into a work book the VBA no longer works for the 72 sheets.

thanks

Sub DoAll()

'Get Sheet Names and paste in sheet1
Dim ws As Worksheet
Dim x As Integer
x = 1

Sheets("Sheet1").Range("A:A").Clear

For Each ws In Worksheets
     Sheets("Sheet1").Cells(x, 1) = ws.Name
     
        If Not ws.Name = "ProF" Then

'Copy Paste and adjust Columns etc.

    Sheets("ProF").Select
    Range("B2:k2").Select
    Selection.Copy
    Sheets(ws.Name).Select

    Range("A1:B1").Select
    Range("B1").Activate
    Range(Selection, Selection.End(xlDown)).Select
    
Dim LRow As Long
Dim lCol As Long
    
    LRow = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row
    
    'MsgBox "Last Row: " & lRow
    
    Range("B1:B" & LRow).Select
    ActiveSheet.Paste
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Sheets("ProF").Select
    Range("C1:K1").Select
    Selection.Copy

    Sheets(ws.Name).Select
    Range("C1").Select
    ActiveSheet.Paste
    Columns("B:K").EntireColumn.AutoFit
    Range("B2:K" & LRow + 1).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False

'Range("M1"). Value = "=CELL("filename")"
Range("L1").Value = "=RIGHT(M1,4)"
Range("L2").Value = "=LEFT(L1,2)"
'Range("J2"). Value = "=LEFT(A2,6)&" "&L2""
Range("n2").Value = "Testing"
 
 
  Application.CutCopyMode = False
        Range("A1").Select
    

End If
x = x + 1
Next ws

End Sub

 

[jorgan202]

The "ProF" sheet has two rows with data that I want to copy to the other 72 sheets, row one is headings and row two has formulas that is then copied to the respective columns, and then copied down till the last row of data. I then change that selection to values. Two other issue I face is :

[COLOR=rgb(209, 72, 65)]Range("M1"). Value = "=CELL("filename")"[/COLOR]
Range("L1").Value = "=RIGHT(M1,4)"
Range("L2").Value = "=LEFT(L1,2)"
[COLOR=rgb(226, 80, 65)]Range("J2"). Value = "=LEFT(A2,6)&" "&L2""[/COLOR]
Range("n2").Value = "Testing"

Red code says its expecting an END statement? No clue what that means.

Thanks