A bit long - but please help

[a]

Hello,

I am really having a problem with the following. I can get most of this
to work but I'm having problems assigning the macros to the buttons.

I have the file I'm trying to update as a variable. I know that I have
gotten this to work before, but I can't now. For some reason the macro
will look to the workbook where I have my procedure rather than to the
file that I'm trying to update. You can see that I am trying both - and
either the one with the variable won't work or the one without will
reference the workbook with the procedure.

As always, any help would be much appreciated.

Regards,
Anita





Sub CopyProc()
Dim SourceModule As VBIDE.CodeModule
Dim DestModule As VBIDE.CodeModule
Dim wB As String, wBCopy As String
Dim sourcePath As String
Dim destPath As String
Dim frow
Dim NewFileColumn
Dim OldFileColumn
Dim SourceDir
Dim DestDir
Dim TableRow
Dim sourceData
Dim Budget
Dim PayrollFile
Dim VarianceFile
Dim SourceDirectory
Dim DestinationDirectory
Dim fileWextention
sourceData = "CostCenters"
Budget = 1
PayrollFile = 2
VarianceFile = 3
SourceDirectory = 4
DestinationDirectory = 5
TableRow = 2 'tableRow is the row of the first file on the FileList sheet

Workbooks("AddingModule2.xls").Activate
wB = Sheets(sourceData).Cells(TableRow, VarianceFile)

Do
Application.ScreenUpdating = False
'wBCopy = Sheets(sourceData).Cells(TableRow, VarianceFile)
fileWextention = wB & ".xls"
Application.StatusBar = "Processing " & fileWextention
sourcePath = Sheets(sourceData).Cells(TableRow, SourceDirectory)
destPath = Sheets(sourceData).Cells(TableRow, DestinationDirectory)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Workbooks.Open Filename:=fileWextention, updatelinks:=False
Workbooks(fileWextention).Activate
ActiveWorkbook.Unprotect ("nope")
'Try This

Dim VBComp As VBComponent
Set VBComp = _

Workbooks(fileWextention).VBProject.VBComponents.Add(vbext_ct_StdModule)
VBComp.Name = "CarAllowance"
Application.Visible = True
'End Try

Set SourceModule = _
Workbooks("AddingModule2.xls").VBProject.VBComponents("Module3").CodeModule

Set DestModule = _
Workbooks(fileWextention).VBProject.VBComponents("CarAllowance").CodeModule



Range("w11").Select

ActiveSheet.Buttons.Add(1123, 231.6, 129.75, 14.25).Select

Selection.Characters.Text = "Benefits"
With Selection.Characters(Start:=1, Length:=13).Font
.Name = "MS Sans Serif"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.OnAction = fileWextention & "!Benefits"
Range("v11").Select

ActiveSheet.Buttons.Add(1272, 231.7, 129.75, 14.25).Select

Selection.Characters.Text = "Print Benefits"
With Selection.Characters(Start:=1, Length:=19).Font
.Name = "MS Sans Serif"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.OnAction = "Benefitsprint"
Range("w11").Select





'adding sheet
Sheets("Bank_Charges").Copy Before:=Sheets(3)

Sheets("Bank_Charges (2)").Name = "Benefits"
ActiveSheet.Unprotect password:="nope"
Range("b10").Value = "Notepad for Benefits"

Range("h10").Value = "80800"


ActiveSheet.Shapes("Button 2").Select
Selection.OnAction = "Home_Benefits"


Cells.Select
Selection.Replace What:="Bank_Charges", Replacement:="Benefits",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False










ActiveSheet.Protect password:="nope"





'changing cells to linked rather than hard coded

Sheets("xxxx").Select
Sheets("xxxx").Unprotect password:="nope"



'benefit cells

Range("N24:O24").Copy Destination:=Range("n19")
Range("N20").Select
Selection.Copy
Range("N19:O19").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("N19:O19").Select
Selection.Replace What:="Insurance", Replacement:="Benefits", LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False




ActiveSheet.Protect password:="nope"
ActiveWorkbook.Protect password:="nope"
Workbooks(fileWextention).Save
Workbooks(fileWextention).Close

TableRow = TableRow + 1
Workbooks("AddingModule2.xls").Activate
wB = Sheets("CostCenters").Cells(TableRow, VarianceFile)
Application.StatusBar = ""
Loop Until wB = ""
Application.StatusBar = ""
End Sub