I have a workbook with 20+ sheets that I am going to split into individual files using VBA. The workbook has some code in ThisWorkbook that inserts rows by double-clicking where you want to insert them. This needs to be added to each workbook that is created when the file is split and saved. I've searched for answers quite a bit and am baffled as to how to make it work. I would really appreciate any help you can give me.
The code I would like to add to ThisWorkbook in the newly created files is:
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) Cancel = True With Target .Offset(1).EntireRow.Insert .EntireRow.Copy .Offset(1).EntireRow(1) With .Offset(1).EntireRow .Cells(1).Resize(, 12).ClearContents On Error Resume Next .SpecialCells(2).ClearContents On Error GoTo 0 End With End With End Sub
This is the code I am using to split the file:
Sub SplitWB() Dim sht As Worksheet Dim newFileName As String Const workBookPath = "U:\2016 Excel Projects\01-Split cash sheets into stations project\" For Each sht In ActiveWorkbook.Worksheets sht.Copy newFileName = workBookPath & sht.Name & ".xlsm" ActiveWorkbook.SaveAs Filename:=newFileName, _ FileFormat:=xlNormal, CreateBackup:=False ActiveWindow.Close Next End Sub