Hi.
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:
Code
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
Display More
This is the code I am using to split the file:
Code
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
Display More
Thanks again!