Run a macro on a different wbk

[av8tordude]

Can someone assist with creating a code that will run the code below on a workbook-B. Thanks

Sub Abbreviation2States()
Dim c As Range
For Each c In ActiveSheet.Range("A:A")
Select Case UCase(c)
Case "AL"
c = "Alabama"
Case "AZ"
c = "Arizona"
Case "AR"
c = "Arkansas"
Case "CA"
c = "California"
Case "CO"
c = "Colorado"
Case "CT"
c = "Connecticut"
Case "DE"
c = "Delaware"
Case "DC"
c = "DISTRICT OF COLUMBIA"
Case "FL"
c = "Florida"
Case "GA"
c = "Georgia"
Case "ID"
c = "Idaho"
Case "IL"
c = "Illinois"
Case "IN"
c = "Indiana"
Case "IA"
c = "Iowa"
Case "KS"
c = "Kansas"
Case "KY"
c = "Kentucky"
Case "LA"
c = "Louisiana"
Case "ME"
c = "Maine"
Case "MD"
c = "Maryland"
Case "MA"
c = "Massachusetts"
Case "MI"
c = "Michigan"
Case "MN"
c = "Minnesota"
Case "MS"
c = "Mississippi"
Case "MO"
c = "Missouri"
Case "MT"
c = "Montana"
Case "NE"
c = "Nebraska"
Case "NV"
c = "Nevada"
Case "NH"
c = "New Hampshire"
Case "NJ"
c = "New Jersey"
Case "NM"
c = "New Mexico"
Case "NY"
c = "New York"
Case "NC"
c = "North Carolina"
Case "ND"
c = "North Dakota"
Case "OH"
c = "Ohio"
Case "OK"
c = "Oklahoma"
Case "OR"
c = "Oregon"
Case "PA"
c = "Pennsylvania"
Case "RI"
c = "Rhode Island"
Case "SC"
c = "South Carolina"
Case "SD"
c = "South Dakota"
Case "TN"
c = "Tennessee"
Case "TX"
c = "Texas"
Case "UT"
c = "Utah"
Case "VT"
c = "Vermont"
Case "VA"
c = "Virginia"
Case "WA"
c = "Washington"
Case "WV"
c = "West Virginia"
Case "WI"
c = "Wisconsin"
Case "WY"
c = "Wyoming"
End Select
Next c
End Sub

[Kenneth Hobs]

For Each c In Workbooks("workbook-B.xls").Worksheets("Sheet1").Range("A:A")

[av8tordude]

Sorry Ken, I should have been clear. I have a userform with button, I would like to open wbk-b, execute the code, then close it.

[av8tordude]

The code is in wbk-a. I manage to open wbk-b. I want to excecute this code (which is wbk-a) on wbk-b.

[Kenneth Hobs]

Now I'm confused. If you want to run a macro in another "open" workbook from another, use:
Application.Run()

[av8tordude]

ok...i'm getting a Type mismatch on your code ken.

For Each c In Workbooks(txtFile).Worksheets("Sheet1").Range("A:A")

[av8tordude]

the code is in wbk-a. I want to open wbk-b, run the code that is in wkb-a on wbk-b

[av8tordude]

i've attached the workbook.

[Kenneth Hobs]

Always structure your code and set VBA to always use:
Option Explicit
Those two practices will help you spot problems early.

The example that I gave you works. What you did was to include the drive and path for txtFile. You can use a function to get the base name like ken.xls rather than c:\ken.xls or when you open the workbook in the code:
Option Explicit
Private txtFile as String
'in your Sub after opening the workbook:
txtFile = ActiveWorkbook.Name

Of course assumptions are made with no error checks. My code also assumed that Sheet1 is the name of a sheet's tab on txtFile.

[av8tordude]

It say Member already exist in object...

Highlights txtFile As String

[Kenneth Hobs]

I know. You have something odd going on with that file. Change txtFile to tFile.