VBA - is this possible? - Create new sheets based on dates submitted in macro

[vpan16]

Hello everyone,

I am not sure how to do this or even if it is possible.
I have 2 sheets.
First sheet has in A1:B4 a table where in A1:A4 is listed "Name" "Company" "Date" and "ID#"
B1:b4 is where I fill in that information for the information i get.

Then. B7 onwards I have a list of Brokers that the above information is applied to.

In sheet 2, I have in column b, a big list of brokers. A macro on sheet 1 matches the brokers list from B7 onwards, to this list and pastes B1:b4 in a new row underneath each broker in sheet 2, starting from column C.

so looks like this for example (starting in column B, column A has a count forumula)

Broker	Name	Company	Date	ID#	Entitles?
Broker 1					YES
	Name1	Company1	6/26/16	##
Broker 2
	Name1	Company1	6/26/16	##

<tbody>
</tbody>

So these two brokers would have been listed in sheet 1, and then the info from B1:b4 would have pasted aross. Every time new entries are uploaded it is in a new row below the brokers.

Now what I want to know is if it possible during the "add request" macro in sheet 1, to check in sheet 2 if the entitles column has a "YES" in it. If it does have a yes, I want a new sheet created, based on the date being inputted in B3 sheet 1 that posts all the rows with the matching dates and YESes.

So for the above table, broker 2 does not have a yes, but broker 1 does. I want a new sheet called June 26, that has the same information listed. IF, a sheet already exists for the 26th, then just post the entries in the next open row, if the sheet doesnt exist, create a sheet, create the headers and then paste the rows. I want the data sorted by the dates essentially, whenever I hit the macro, just for the information that says "YES".

How do I proceed? I dont know how to name sheets based on cells and how to even move forward with this.

I can post teh existing macro's code too, if anyone needs it, but I feel it would be best to create a new sub and then call it in the other code.

 

[cerfani]

Now what I want to know is if it possible during the "add request" macro in sheet 1, to check in sheet 2 if the entitles column has a "YES" in it. If it does have a yes, I want a new sheet created, based on the date being inputted in B3 sheet 1 that posts all the rows with the matching dates and YESes.

yes... you need to add something like this... (do not copy paste as i am not writing valid code...pseudocode)

With Workbooks("WB_With_Sheet_2.xlsx").Sheets("Sheet2")

    rowStart = 2 'i assume your header is in row 1
    lastRow = .Cells(.Rows.Count,2).End(xlUp).Row '.Rows.Count should return 1048576 for xlsx and 65335 for xls, use .Rows for BC

    For r = rowStart To lastRow
        If .Cells(r, 2).Value2 = "Yes" Then
            MsgBox "kthxbye"
        End If
    Next r

End With

on the sheet where you are inputting dates, make a worksheet change event and check for edits to B3 and then have it run the pseudo code when it is B3... just reference the range properly and you can move a value from a range in any sheet in any workbook to another range in any sheet in any workbook

 

[vpan16]

problem is, it doesnt have the information written in teh same row with the "YES" its always in rows below the YES
and
how do I create a new sheet where it is named based on the date inputted? ie how to translate 6/26/16 to "June 26" as sheet title and then match all future dates to check for existing sheets and if sheet date doesnt exist, to paste new sheet with the date?

Do my questions make sense?

 

[cerfani]

problem is, it doesnt have the information written in teh same row with the "YES" its always in rows below the YES
and
how do I create a new sheet where it is named based on the date inputted? ie how to translate 6/26/16 to "June 26" as sheet title and then match all future dates to check for existing sheets and if sheet date doesnt exist, to paste new sheet with the date?

Do my questions make sense?

this will check every value in row 2 and if yes is there you get a msgbox, substitute msgbox with setting values from one range to another

For r = rowStart To lastRow
    If .Cells(r, 2).Value2 = "Yes" Then
        MsgBox "kthxbye"
    End If
Next r

 

[cerfani]

you need to use these functions to convert dates in one format to another

VBA Date-Time Function

VBA Month Name

VBA Day Function

builkd your string by concatenating the values returned by these functions

concatenation example

Dim s As DateTime
s = ... *see note
Dim dateText as String
dateText = MonthName(s) & " " & Day(s)

*you can probably get from a cell value then use a function like http://www.tutorialspoint.com/vba/vba_timevalue_function.htm to create a datetime to use in vba

this function will probably do it all for you... http://www.tutorialspoint.com/vba/vba_formatdatetime_function.htm

 

[vpan16]

thank you for all your help,
As you can probably tell, I'm a beginner in VBA, but I will try and look into what youve told me and see if I can make some sense and a code out of it

 

[cerfani]

i just noticed... VBA Time Value Function

is not for creating datetime objects in vba. I think you need to use CDate instead. In VBA, or any kind of programming... you need to convert your data type to a data type which exposes functionality to manipulate or use said data in a way you want.

So you will want to convert a string representation of a date to a Date object. Google that.

String -> Date

 

[cerfani]

if you have "6/26/2016" in a cell (A1) then do this in vba

Dim d as Date
d = CDate(Range("A1").Value2)

now with d you can use all those date functions that ask for a Date object... you created a Date object from the value in cell A1 that was technically a string representation (or maybe it goes into vba as a serial number)

edit... VBA will interpret that value as a serial number if it is formatted as a date since that is the underlying value of dates on a worksheet

 

[vpan16]

if you have "6/26/2016" in a cell (A1) then do this in vba

Dim d as Date
d = CDate(Range("A1").Value2)

now with d you can use all those date functions that ask for a Date object... you created a Date object from the value in cell A1 that was technically a string representation (or maybe it goes into vba as a serial number)

edit... VBA will interpret that value as a serial number if it is formatted as a date since that is the underlying value of dates on a worksheet

okay, thanks!

 

[vpan16]

Im having confusion with the "change event" part.

This is the code I have for the macro copying values from sheet 1 to sheet 2.

Sub CopyData1()    Dim r As Range
    Dim tgt As Range
    Dim data As Range
    Dim cel As Range
    Dim c As Range
    Dim WS1 As Worksheet
    Dim WS2 As Worksheet
    Dim WS3 As Worksheet
    Set WS1 = Worksheets("Add Request")
    Set WS2 = Worksheets("Sorted by Broker")
    Set WS3 = Worksheets("Wishlist - Brokers")
    Set r = WS1.Columns(1).Find("Brokers")(2)
    Set r = WS1.Range(r, WS1.Cells(WS1.Rows.Count, 1).End(xlUp))
    Set data = WS2.Columns(2)
    For Each cel In r
    Sheets("Sorted by Broker").Select
        Set c = data.Find(cel)
        If c Is Nothing Then
            Set tgt = WS3.Columns(2)
            Set tgt = tgt.Find(cel.Value)
            If tgt Is Nothing Then
                Set tgt = WS3.Cells(WS3.Rows.Count, 3).End(xlUp).Offset(1, -1)
                tgt.Value = cel.Value
            End If
            tgt.Offset(1).EntireRow.Insert
            tgt.Offset(1, 1).Resize(1, 5).Value = Application.Transpose(WS1.Range("B1:B5"))
        Else
           c.Offset(1).EntireRow.Insert
           c.Offset(1, 1).Resize(1, 5).Value = Application.Transpose(WS1.Range("B1:B5"))
        End If
        Sheets("Wishlist - Brokers").Select
    Next
End Sub

How do I set a change event? I dont want the code that creates the new sheets to run whenever i change B3, but whenever I run the code above.