VBA Excel 2007

[ubh8n2]

All,

I have been trying to get this vba to work all weekend that I found online and I need help. When I fill the the "User defined" values in the variables tab and I click the replace button it is suppose to change these values in the refence tab.

This is the Code:

[VBA]Public Sub ReplaceValues(OriginalSheetName As String, VariableSheetName As String, NewSheetName As String)
Dim iVariableRowCounter As String
Dim sSearchValue As String
Dim sReplacementValue As String
Dim iControlCounter As Integer

ThisWorkbook.Sheets(OriginalSheetName).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name = NewSheetName

For iControlCounter = ThisWorkbook.Sheets(NewSheetName).Shapes.Count To 1 Step -1
If ThisWorkbook.Sheets(NewSheetName).Shapes(iControlCounter).Type = msoOLEControlObject Then
ThisWorkbook.Sheets(NewSheetName).Shapes(iControlCounter).Delete
End If
Next

iVariableRowCounter = 2

While ThisWorkbook.Sheets(VariableSheetName).Cells(iVariableRowCounter, 1).Value <> ""
sSearchValue = ThisWorkbook.Sheets(VariableSheetName).Cells(iVariableRowCounter, 1).Value
sReplacementValue = ThisWorkbook.Sheets(VariableSheetName).Cells(iVariableRowCounter, 2).Value

ThisWorkbook.Sheets(NewSheetName).UsedRange.Replace what:=sSearchValue, replacement:=sReplacementValue, searchorder:=xlByColumns, MatchCase:=False, lookat:=xlPart

iVariableRowCounter = iVariableRowCounter + 1
Wend
End Sub
Public Function GenerateNewWorksheetName(OriginalSheetName As String) As String
Dim sNewSheetName As String
Dim iIncrement As Integer
Dim iSheetCounter As Integer
Dim bGoodName As Boolean
Dim bSheetFound As Boolean

iIncrement = 1
bGoodName = False

While Not bGoodName
sNewSheetName = OriginalSheetName & " - " & iIncrement
bSheetFound = False

For iSheetCounter = 1 To ThisWorkbook.Sheets.Count
If ThisWorkbook.Sheets(iSheetCounter).Name = sNewSheetName Then
bSheetFound = True
Exit For
End If
Next

If Not bSheetFound Then
bGoodName = True
End If

iIncrement = iIncrement + 1
Wend

GenerateNewWorksheetName = sNewSheetName
End Function[/VBA]

I will appreciate any help I can get.

[Bob Phillips]

Can you give a bit more detail about what is supposed to happen, what those procedures are meant to do (I don't have the stomach to figure it all out from the bottom).

[ubh8n2]

All of the values I enter on colum b in the variables tab is suppose to fill in the reference tab when I click on the replace button. For instance: column A [Switch-Name] I enter value in column b: MS-MDF-Chicago and it is suppose to change in the reference tab. I have a lot of switches that I have to configure and this is something that I found online. I need to change hostname and some IP Address values so that I can load the configs to the switches. Hope this helps.

[mancubus]

button is asigned to
[VBA]Public Sub ReplaceValues(OriginalSheetName As String, VariableSheetName As String, NewSheetName As String)[/VBA]

you may play around with:
[VBA]
Sub Call_My_Macro
ReplaceValues("SheetOrg", "SheetVar", SheetNew")
End Sub
[/VBA]

[Paul_Hossler]

1. There were 2 modules that had the same subs in so I deleted #2
2. I like to use Option Explicit (since I can't type)
3. There was something wrong with the button, so I created a new one and used your old code on it

It seems to run when I click the "Replace" button, and makes a new sheet.

That's really as far as I got with the logic, etc.

Paul