Johnny Thunder
Well-known Member
- Joined
- Apr 9, 2010
- Messages
- 693
- Office Version
- 2016
- Platform
- MacOS
Hello All,
I am in search of a greater being who possesses the power to help me with my macro.
I will tell you what it needs to do and what I have come up with so far.
1.Based on Sheet "Main" Cell "B23" which is a drop down list of File names that correspond to Company names.
2.Based on Sheet "Main" Cell "B19" which is a drop down list of tab names that correspond to standerized tab names of multiple files from the drop down list above in cell "B23".
3. If Sheets "Main" Cell "B19" = "ODM" then go to sheet "Agreement" and copy range ("B14:H" & Lastrow) Last row being the last row of data in column H.
4. Open Workbook which is defined from Sheets "main" Cell "B23" and Goto Specific tab which is defined in original workbook Sheets "Main" Cell "B19".
5. If Sheets "Main" Cell "B19" = "ODM" then in NewWorkBook PasteValues in Range ("C14:I" & Lastrow) *I think thats how it should be written?
6.then I need it to ElseIF the New Workbook isn't a .XLSM then redo all the same steps but look for .XLS instead.
7. And I will have a few other #3 that will be different names other than "ODM" which correspond to different ranges.
**I hope I was very descriptive in what I am trying to accomplish. I know some of what I am trying to do isn't in my current macro but it's what the end result should be.
Hopefully someone might be able to simplify my macro or at least point me in the right direction to work with this.
Thanks in advance!!
My Code-
Sub OpenWorkbook()
Dim LastRow As Long
Dim w As Workbook
'Defines file name
varCellvalue = Sheets("Main").Range("B23").Value
'Defines Type of agreement and assigns Sheet to find
VarCell = Sheets("Main").Range("B19").Value
Set w = ActiveWorkbook
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
w.Activate
If Sheets("main").Range("B19") = "ODM" Then
Sheets("Agreement").Activate
Range("B14:H" & LastRow).Select
Selection.Copy
ElseIf Sheets("main").Range("B19") = "MPA" Then
Sheets("Agreement").Activate
Range("B14:K" & LastRow).Select
Selection.Copy
ElseIf Sheets("main").Range("B19") = "ODM" Then
Sheets("Agreement").Activate
Range("B14:H" & LastRow).Select
Selection.Copy
Else
End If
Sheets("Main").Activate
If Not IsEmpty(Range("B23").Value) Then
' Opens the workbook based on company name
Workbooks.Open "\\Lax-Netapp01\Dept_private\Business Systems\LFC MACRO TEST\" & varCellvalue & ".xlsm"
'selects sheet based on agreement type from "Main" tab
Workbooks(varCellvalue & ".xlsm").Sheets(VarCell).Activate
Range("C14:H" & LastRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Else
Workbooks.Open "\\Lax-Netapp01\Dept_private\Business Systems\LFC MACRO TEST\" & varCellvalue & ".xls"
Workbooks(varCellvalue & ".xls").Sheets(VarCell).Activate
End If
End Sub
I am in search of a greater being who possesses the power to help me with my macro.
I will tell you what it needs to do and what I have come up with so far.
1.Based on Sheet "Main" Cell "B23" which is a drop down list of File names that correspond to Company names.
2.Based on Sheet "Main" Cell "B19" which is a drop down list of tab names that correspond to standerized tab names of multiple files from the drop down list above in cell "B23".
3. If Sheets "Main" Cell "B19" = "ODM" then go to sheet "Agreement" and copy range ("B14:H" & Lastrow) Last row being the last row of data in column H.
4. Open Workbook which is defined from Sheets "main" Cell "B23" and Goto Specific tab which is defined in original workbook Sheets "Main" Cell "B19".
5. If Sheets "Main" Cell "B19" = "ODM" then in NewWorkBook PasteValues in Range ("C14:I" & Lastrow) *I think thats how it should be written?
6.then I need it to ElseIF the New Workbook isn't a .XLSM then redo all the same steps but look for .XLS instead.
7. And I will have a few other #3 that will be different names other than "ODM" which correspond to different ranges.
**I hope I was very descriptive in what I am trying to accomplish. I know some of what I am trying to do isn't in my current macro but it's what the end result should be.
Hopefully someone might be able to simplify my macro or at least point me in the right direction to work with this.
Thanks in advance!!
My Code-
Sub OpenWorkbook()
Dim LastRow As Long
Dim w As Workbook
'Defines file name
varCellvalue = Sheets("Main").Range("B23").Value
'Defines Type of agreement and assigns Sheet to find
VarCell = Sheets("Main").Range("B19").Value
Set w = ActiveWorkbook
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
w.Activate
If Sheets("main").Range("B19") = "ODM" Then
Sheets("Agreement").Activate
Range("B14:H" & LastRow).Select
Selection.Copy
ElseIf Sheets("main").Range("B19") = "MPA" Then
Sheets("Agreement").Activate
Range("B14:K" & LastRow).Select
Selection.Copy
ElseIf Sheets("main").Range("B19") = "ODM" Then
Sheets("Agreement").Activate
Range("B14:H" & LastRow).Select
Selection.Copy
Else
End If
Sheets("Main").Activate
If Not IsEmpty(Range("B23").Value) Then
' Opens the workbook based on company name
Workbooks.Open "\\Lax-Netapp01\Dept_private\Business Systems\LFC MACRO TEST\" & varCellvalue & ".xlsm"
'selects sheet based on agreement type from "Main" tab
Workbooks(varCellvalue & ".xlsm").Sheets(VarCell).Activate
Range("C14:H" & LastRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Else
Workbooks.Open "\\Lax-Netapp01\Dept_private\Business Systems\LFC MACRO TEST\" & varCellvalue & ".xls"
Workbooks(varCellvalue & ".xls").Sheets(VarCell).Activate
End If
End Sub