Macro to run differently depending on text in drop down cell.

[Luke1690]

Hi,

i Think this should be an easy one for you guys.

i have made a macro that copies data from (workbook1) to a specific workbook, (Workbook A) related to that area. To activate the macro i have used a button.

i have had to duplicate workbook1 several times and change the code so the data can copy to other specific workbooks A,B,C,D related to different zones, when a button is clicked.

What i would like is one copy of (workbook1) but when the name of a workbook is chosen from a dropdown list for example (workbookA) the data is copied to that workbook, after the button click. (Only one button please)

*drop down can be any cell*

i cant give you an example gdpr....

Thanks Guys i hope that makes sense...... basically determine how the code is run/or what code is run by text from cell. using only one button!

 

[6StringJazzer]

Show us the code you have now, and we'll show you how to revised it. Also tell us what sheet and what cell your dropdown is in.

I recommend using CODE tags when you post code. Paste the code into your post, select the code, then click the VBA icon in controls to mark it as code.

 

[Luke1690]

sub emaildatatoworkbookA

Dim emailapplication As Object
Dim emailitem As Object

Set emailapplication = CreateObject("outlook.application")
Set emailitem = emailapplication.createitem(0)

Workbooks("workbook1.xlsm").Save

'now we build the email.

emailitem.to = "EMAILADDRESS"

emailitem.Subject = Range("d14").Value

emailitem.body = "Reorder "


emailitem.attachments.Add ActiveWorkbook.FullName

emailitem.send

Workbooks.Open "\Location of workbookA"

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

'Set variables for copy and destination sheets
Set wsCopy = Workbooks("workbook1.xlsm").Worksheets("Sheet1")
Set wsDest = Workbooks("workbookA.xlsm").Worksheets("Sheet1")

'1. Find last used row in the copy range based on data in column A
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "M").End(xlUp).Row

'2. Find first blank row in the destination range based on data in column
'Offset property moves down 1 row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "B").End(xlUp).Offset(1).Row

'3. Copy & Paste Data
'wsCopy.Range("M6:U" & lCopyLastRow).copy wsDest.Range("B" & lDestLastRow)
wsCopy.Range("M6:Z" & lCopyLastRow).Copy
wsDest.Range("B" & lDestLastRow).PasteSpecial Paste:=xlPasteValues

Workbooks("WorkbookA").Save


End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address(0, 0) = "D12:K12" Then
Target.Value = Format(Date, "ddd")
End If

If Target.Address(0, 0) = "D13:K13" Then
Target.Value = Now()
End If
If Not Intersect(Target, Range("A10:D10")) Is Nothing Then

Target.Value = Application.UserName
Cancel = True
End If

 

[6StringJazzer]

Also tell us what sheet and what cell your dropdown is in.

You missed this one.

 

[Luke1690]

You missed this one.

Its dosnt matter mate put it sheet1 and A1. I just want to see the lines of code I will change the ranges and sheets to suit.

Thanks alot.

 

[6StringJazzer]

Add red code, delete strikeout code

Workbooks.Open "\Location of workbookA"
Dim wbDest As Workbook
Set wbDest = Workbooks.Open "\folder path for workbook\" & Worksheets("Sheet1").Range("A1")

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

'Set variables for copy and destination sheets
Set wsCopy = Workbooks("workbook1.xlsm").Worksheets("Sheet1")
Set wsDest = Workbooks("workbookA.xlsm").Worksheets("Sheet1")
Set wsDest = wbDest.Worksheets("Sheet1")

 

[Luke1690]

I'm very confused on how that works? How does that change depending on what is picked from a drop down?

 

[6StringJazzer]

Your dropdown is in Sheet1, cell A1. The line of code that opens the file uses the contents of that cell as the file name, instead of hardcoding File A. This code also assigns that newly-opened file to the variable wbDest. Then it sets your already-existing variable wsDest to Sheet1 of that file, and the rest of your code is the same.

 

[6StringJazzer]

Oh, I missed one change.

Workbooks("WorkbookA").Save
wbDest.Save

 

[Luke1690]

Thanks for your help Jeff.

Really appreciated.