Need a VBA GURU!!! - Macro help copying and pasting data

[Johnny Thunder]

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

 

[James Snyder]

You have to use the [] with code in the middle to start, and [/] with code after the slash to end.

EDIT: Details are in the faq at the top of the forum.