New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Copy code from one worksheet to another

Free Excel Tips and Tutorials

Copy code from one worksheet to another

Postby Apcbr » Sun May 06, 2012 1:22 pm

I used the following code to copy the macro's in one worksheet to a worksheet in another workbook.
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
Greetings,
Apcbr

Say what you mean, mean what you say :-)
Apcbr
Regular
 
Posts: 38
Joined: Apr 25, 2012
Reputation: 0
Excel Version: 2007

Return to Excel Tips and Tutorials

Who is online

Users browsing this forum: No registered users and 4 guests