Solved Excel Macro to copy user specified data to new worksheet

July 19, 2013 at 12:02:58
Specs: Windows 64
I am trying to create a macro that searches for a range of dates by prompting the user for a start and end date and then searches a specific column by asking the user to enter the column header title. I then want to copy the data from that row for 4 specific columns called LOB, Profile, FirstName, and LastName and paste it in a new workbook. The remaing data in that row should not be copied. I have limited VB knowledge and looking for somebody to help. Thanks!

See More: Excel Macro to copy user specified data to new worksheet

Report •


#1
July 19, 2013 at 12:46:01
re: "I have limited VB knowledge and looking for somebody to help."

Does "limited knowledge" mean that you can take some generic code and modify it to fit your specific needs (change search ranges, sheets names, etc.) or are you looking for a complete and working macro?

If it's the former, I can throw something together. If it's that latter, I would need some more specific information, like what columns the dates are in, exactly where should the data should be pasted, etc.

As it stands now, I'm a little confused by a few of things.

1 - Your subject line says "copy user specified data to new worksheet", the text of your post says "paste it in a new workbook" As I'm sure you know, those are very different things and the code required will be very different.

2 - You said "by prompting the user for a start and end date and then searches a specific column by asking the user to enter the column header title."

Does that mean that you have dates in more than one column and the code will need to know which column to use each time a search is done?

3 - Will multiple rows need to be copied for each search or will it be a single row based on a single date that will be found based on the start and end dates. In other words, will there typically be more than one date found between the start and end dates?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 22, 2013 at 07:52:20
Sorry for the confusion, hopefully I have provided the needed details below:
1 - I would prefer the data is copied into a new workbook. The workbook can be called anything.

2 - You are correct in that I usually have 2 to 4 columns with dates in them and would like to be able to specify which column to search. These columns aren't always in the same place. As with the date range I could specify a column A, B, C, etc. on a separate worksheet or prompt the user for the Column letter.

3 - I need multiple rows copied as I have hundreds of lines with the same dates. When the data is copied I want to copy everything within a certain date range; like from 7/1/13 to 8/1/13 and I would have at least 3 different dates that match hundreds of records between those dates. I can either put the date range on a separate worksheet in the same workbook or the macro could prompt the user for the date range. The column of dates will be sorted from oldest to newest before running the macro.

If I have a working macro I can usually understand the code and update column and/or sheet names as needed.


Report •

#3
July 23, 2013 at 13:26:49
✔ Best Answer
Here's a first shot at some code. I did some testing, but nothing extensive.

1 - The user must select the column with the dates to be searched by either selecting the column letter or selecting a cell or cells in the column with the dates. Manually entering a Column letter will not work.
2 - The user can choose the date range by either selecting a cell with the start date and end date or manually entering a start and end date in the InputBoxes.
3 - The user can quit by clicking Cancel in any InputBox.

There isn't much error checking so it's certainly not user-proof. As written, the code doesn't care if there are no dates in the column selected or if the Start Date is after the End Date or if the Start and End dates aren't even dates. All of that can be dealt with later, once you decide that the code does essentially what you want. Try it in a perfect world where users don't make mistakes, then try to make as many errors as you think your users will make. Once you see what it does in each case, the code can be finessed to deal with those situations.

Sub CopyDateRangeData()
'Get Date Column from User, Quit if Cancelled
'User can click on Column letter or anywhere in the Date Column
On Error Resume Next
   dateCol = Application.InputBox("Please Click The Column To Be Searched", Type:=8).Column
     If dateCol = False Then Exit Sub
'Get Start Date from User, Quit if Cancelled
'User can click on Date in cell or Enter date manually
   dateStart = Application.InputBox("Please Enter Start Date For Search")
     If dateStart = False Then Exit Sub
'Get End Date from User, Quit if Cancelled
'User can click on Date in cell or Enter date manually
   dateEnd = Application.InputBox("Please Enter End Date For Search")
     If dateEnd = False Then Exit Sub
''If we've made it this far, then create new workbook
'Save current workbook name
    curName = ActiveWorkbook.Name
'Add a new workbook and grab it's name
    Workbooks.Add
    newName = ActiveWorkbook.Name
'Activate the original workbook
    Workbooks(curName).Activate
'Determine length of Date column
    With Workbooks(curName).Sheets(1)
      lastDate = .Cells(Rows.Count, dateCol).End(xlUp).Row
'Loop through dates, copying Rows that are within the Date Range entered
        For srcRw = 1 To lastDate
           If .Cells(srcRw, dateCol) >= CDate(dateStart) And _
              .Cells(srcRw, dateCol) <= CDate(dateEnd) Then
'Find next Empty Row in Sheet2
                  nxtRow = _
                     Workbooks(newName).Sheets(1).Range("A" & Rows.Count).End(xlUp).Row + 1
'Copy Row to New Workbook
                   .Cells(srcRw, dateCol).EntireRow.Copy _
                      Destination:=Workbooks(newName).Sheets(1).Range("A" & nxtRow)
           End If
        Next
    End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question