There may be better or easier ways but I couldn't find any.
1. The code you want to copy has to be copied on a worksheet (manually).
2. From the workbook that has this sheet the code is added to an array (variable number of rows and empty rows are ok) -Get_Code()
3. Then the workbook that needs the code has to be opened.
4. The worksheet you want the code in has to be selected and activated.
5. The code is added to the worksheet code - Add_Code().
- Code: Select all
Option Explicit
Dim Long_Code As Variant
Dim Used_Rows As Integer
Sub Get_Code() 'Done before opening next workbook
'Copy the code you want to add to another workbooksheet in column A on sheet2
Dim i As Integer
Used_Rows = 0
With ActiveWorkbook.Worksheets("Sheet2") 'code is present on this sheet
On Error Resume Next
'find the last non empty row, this is the number of rows to add to the array
Used_Rows = .Cells.Find("*", .Cells(1), xlFormulas, xlWhole, xlByRows, xlPrevious).Row
If Err <> 0 Then Used_Rows = 0
End With
ReDim Long_Code(1 To Used_Rows) As String
For i = 1 To Used_Rows
Long_Code(i) = "" & ActiveCell.Offset(i - 1, 0).Value & "" 'double quotes to add a quote to the cellvalue
Next i
ActiveWorkbook.Sheets("Sheet1").Select
End Sub
Sub Add_Code()
'Before running this part, open the workbook and activate the sheet you want to code in
Dim NextLine, i As Integer
With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
NextLine = .CountOfLines
For i = 1 To Used_Rows
.InsertLines NextLine + i, Long_Code(i)
Next i
End With
End Sub
Sub Do_It()
'This is where you call the procedures and open the workbook and so on.
Get_Code
'open the target workbook, activate sheet
Add_code
'Do some more stuff if you like
End Sub