472,961 Members | 2,572 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,961 software developers and data experts.

Copy past on multiple criteria like dates and center

I have used following code and run successfully.

Expand|Select|Wrap|Line Numbers
  1. Sub mcopy()
  2.  
  3. a = Worksheets("CRM").Cells(Rows.Count, 1).End(xlUp).Row
  4.  
  5. Dim myModule As String
  6. myModule = Application.InputBox("Enter a Module")
  7.  
  8. For i = 2 To a
  9.     If Worksheets("CRM").Cells(i, 4).Value = myModule Then
  10.  
  11.         Worksheets("CRM").Rows(i).Copy
  12.         Worksheets("MODCRM").Activate
  13.         b = Worksheets("MODCRM").Cells(Rows.Count, 1).End(xlUp).Row
  14.  
  15.         Worksheets("MODCRM").Cells(b + 1, 1).Select
  16.         ActiveSheet.Paste
  17.         Worksheets("CRM").Activate
  18.     End If
  19. Next
  20.  
  21. Application.CutCopyMode = False
  22. 'ThisWorkbook.Worksheets("CRM").Cells(1, 1).Select
  23.  
  24. End Sub
I have use input box in the code, but i want to use cell address where i have dropdown list at "CRM" sheet at Cell Addree(D1).

Pl guide me in the matter.
Sep 9 '18 #1
1 3946
SioSio
272 256MB
Select "View Code" from the right-click menu of the tab of the CRM sheet and write the following code.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.     If Not Intersect(Target, Range("D1")) Is Nothing Then
  3.         a = Worksheets("CRM").Cells(Rows.Count, 1).End(xlUp).Row
  4.         Dim myModule As String
  5.         myModule = Range("D1").Value
  6.         For i = 2 To a
  7.            If Worksheets("CRM").Cells(i, 4).Value = myModule Then
  8.                Worksheets("CRM").Rows(i).Copy
  9.                Worksheets("MODCRM").Activate
  10.                b = Worksheets("MODCRM").Cells(Rows.Count, 1).End(xlUp).Row
  11.                Worksheets("MODCRM").Cells(b + 1, 1).Select
  12.                ActiveSheet.Paste
  13.                Worksheets("CRM").Activate
  14.             End If
  15.         Next
  16.         Application.CutCopyMode = False
  17.         'ThisWorkbook.Worksheets("CRM").Cells(1, 1).Select
  18.     End If
  19. End Sub
  20.  
Dec 24 '19 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: ShyGuy | last post by:
I have a table with 4 fields. Three are used for criteria. I can get the DLookup to work with 1 criteria with the following but can't get it to work with 2 or three. NumofAppts = DLookup("",...
3
by: developing | last post by:
Hello How do I specify multiple criteria for FindFirst or FindRecord (or anything else) that takes the criteria from a form. (text field and number field) edit: this will be in the after...
4
by: JHNielson | last post by:
I have a query that I'm trying to update with a dlookup with multiple criteria This is the string: EVNT_DT: DLookUp("","","( .EVNT_QTR=.) & (.=.)") When i run it it says it can't find the...
1
by: 2D Rick | last post by:
I want to open a report using OpenReport and passing it a Where Clause with multiple criteria. I know the Where Clause below is way off but it conveys what I need. Dates are of string type. ...
0
by: ChadK | last post by:
I am trying to open a report based on what the user selects on a form. Each individual criteria works but when I try to combine to pass multiple criteria it doesn't. I have read what I can find on...
1
by: akirekab | last post by:
I am using DCount, but I am not able to find how to set simple multiple criteria. Here is sample of what i need. =DCount("PatientProfileID","qryFaceToFaceReason_EAP_VG","FaceToFaceReasonID=2"...
3
by: kstevens | last post by:
Please help. I know the sysntax is wrong. Here are some details. I am looking for the sum of past shipped items. I originally wrote this Dsum with only one criteria, but because of multiple...
1
by: Brendan Wolf | last post by:
Happy Halloween all, I have been struggling at work with a DLookup using multiple criteria. I would like a text box to display the results of a DLookup based on the values selected in three...
3
by: Brendan Wolf | last post by:
Happy Halloween all, I have been struggling at work with a DLookup using multiple criteria. I would like a text box to display the results of a DLookup based on the values selected in three...
4
by: DANNYOCEAN | last post by:
I have a code that sucessfully calculates how many numbers are lower than for example 45 , for large sample number list. But i´m looking for a code that have a COUNT function with MULTIPLE...
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
2
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.