Serching Sheet Names in another workbook based on cell criteria in active workbook

FrEaK_aCcIdEnT

Board Regular
Joined
May 1, 2012
Messages
100
I am going to try to explain this as simple as possible.

Active workbook cell (E1) is the source for the search. I need to open a workbook file and search for the sheet name that matches the contents in cell (E1) on the active workbook. Once found I need to copy a range of cells that is pre-selected back into the original active workbook's cell (I5). Then close the workbook file that the search was completed on.

I already have some vba completed to the point of opening the file I need to search. I have pasted below and changed information for security reasons (file name). I have been searching, but have yet come accross what I need.

Thanks in advance!

Code:
Sub SheetName()
'
' SheetName Macro
'
' Keyboard Shortcut: Ctrl+d
'
Dim j As Long
For j = 23 To 9 Step -1
If WorksheetFunction.Max(Range(Cells(9, j), Cells(23, j))) > 2450 Then Columns(j).Delete
Next j
Range("C1").Select
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "=RIGHT(R[2]C[-2],8)"
Range("D1").Select
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "=RIGHT(R[3]C[-3],LEN(R[3]C[-3])-8)"
Range("D2").Select
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "=SUBSTITUTE(R[-1]C[0],""Orig."",1,1)"
Range("E1").Select
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "=LEFT(R[1]C[-1],LEN(R[1]C[-1])-8)"
Range("I5").Select
Workbooks.Open Filename:="REMOVED"
End Sub

This is a template workbook that gets information pasted in from another file manually. Then this vba is setup to delete columns that are not needed, auto rename the sheet name based on a cells contents, then extract a string of alphanumeric text that changes every time and will hopefully use that extracted text to locate a sheet with the matching text on its sheet name and copy the preselected cell range back into the template's cell (I5)

Anyone that can help me out, I would greatly appreciate it! I am still very green at this...
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Watch MrExcel Video

Forum statistics

Threads
1,122,820
Messages
5,598,301
Members
414,224
Latest member
Crazy_FC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top