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

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
693
Office Version
  1. 2016
Platform
  1. 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
 
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.
 
Upvote 0

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
Back
Top