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!
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...
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...