Posts by Brand_New2VBA

Brand_New2VBA · Apr 23rd 2012

Hello VBA Gurus,

So far I have gotten to the root cause of my problem thanks to the great mind on this wonderful forum now I would like to figure out which code will allow my userform to close out once the command button has been hit and task executed. to show my user form I have:

Code

userform1.show

now how to I have it automatically close without having to hit the x at the top right corner?

Your advise and wisdom would be most appreciated.

Brand_New2VBA · Apr 23rd 2012

Re: MACRO that allows user to copy and insert multiple worksheets through userform co

Hi Sooper,

The sheet ("CC Request Form") is hidden. Does that play a part in the error messages? Does it mater is the sheet being copied is hidden?

this is what I have:

Code

Private Sub CommandButton6_Click()


Dim x As Integer
    
    x = ComboBox5.Value
    
    For numtimes = 1 To x
       
       ActiveWorkbook.Sheets("CC Request Form 1").Copy _
After:=ActiveWorkbook.Sheets("Sheet2")


        
    Next


End Sub

Display More

I wish I knew why it works for you and not for me. I still get errors here:

Code

ActiveWorkbook.Sheets("CC Request Form 1").Copy _
After:=ActiveWorkbook.Sheets("Sheet2")

Brand_New2VBA · Apr 23rd 2012

Re: MACRO that allows user to copy and insert multiple worksheets through userform co

Hi Sooper,

The sheet ("CC Request Form") is hidden. Does that play a part in the error messages? Does it mater is the sheet being copied is hidden?

Brand_New2VBA · Apr 23rd 2012

Re: MACRO that allows user to copy and insert multiple worksheets through userform co

forum.ozgrid.com/index.php?attachment/45389/

Hi Sooper,

I've attached a sample file of my project. Please help me understand where I went wrong with the macro. Thank you!

Brand_New

Brand_New2VBA · Apr 23rd 2012

Re: MACRO that allows user to copy and insert multiple worksheets through userform co

how exactly did you wrtie your code?

Brand_New2VBA · Apr 23rd 2012

Re: MACRO that allows user to copy and insert multiple worksheets through userform co

I'm unable to see your code or run the macro on the sheet.

Brand_New2VBA · Apr 23rd 2012

Re: MACRO that allows user to copy and insert multiple worksheets through userform co

Thank you Sooper for your reply however that doesnt work either. I even attenpted to add an if statement within the code and that doesnt work this is what I have now:

Code

Private Sub CommandButton6_Click()
If OptionButton1.Value = True Then
Dim x As Integer
    
    x = ComboBox5.Value
    
    For numtimes = 1 To x
       
       ActiveWorkbook.Sheets("CC Request Form").Copy _
           After:=ActiveWorkbook.Sheets("Sheet7")
        
    Next
End If
End Sub

Display More

the error keeps coming up here:

Code

ActiveWorkbook.Sheets("CC Request Form").Copy _
           After:=ActiveWorkbook.Sheets("Sheet7")

Do you have any other sugestions?

Brand_New2VBA · Apr 23rd 2012

Greetings all VBA Gurus,

My UserForm has a ComboBox that allows users to select the number of forms they would like to add to the current work sheet. Once the numbers of forms are selected, through the ComboBox, I'd like the exact number listed in the ComboBox to be inserted after the third sheet in the workbook. However this code isn’t working and continues to give off errors. Can someone help be debug this problem? Where am I going wrong with this code? Is it possible to insert multiple sheets at a time? Microsoft certainly thinks so however the code continues to elude me.

Your advice and opinions are greatly needed. Thanks in advance guys.

So far this is what I have:

Code

Private Sub CommandButton1_Click()
    Dim x As Integer
    
    x = ComboBox.Value
    For numtimes = 1 To x
        
        ActiveWorkbook.Sheets("CC Request Form").Copy _
           After:=ActiveWorkbook.Sheets("Sheet7")
    Next
 


End Sub

Display More

Sincerely,
Brand_New2VBA

Brand_New2VBA · Apr 20th 2012

Re: Vba code that automatically inserts new sheet after hitting comman button

Gratitude Roy! You're a life saver!

Brand_New2VBA · Apr 20th 2012

Greetings to all my excel gurus and VBA high masters;

I would like to create a VBA code that responds to a command button. The code would basically create a brand new sheet identical to the original sheet prior to any alterations. So for example:

If sheet one is a form that needs to be filled out--which starts off blank--once it has been completed the user can hit the "create new form" button (if he/she needs to complete another form) and a new sheet with a blank form and a "create new form" button will pop up.

So far I'm hitting walls here and I'm not even sure where to begin.

You're wisdom and advice would be greatly appreciated.

Thank you Gurus & VBA masters

Sincerely,
Brand_New2VBA

Brand_New2VBA · Jun 28th 2011

Re: Sorting by date formula with both numerical values and text values

password is auspicious.

Brand_New2VBA · Jun 15th 2011

Re: Sorting by date formula with both numerical values and text values

Can anyone help with my inquiry? Is it possible to sort numerically and alphabetically?

Brand_New2VBA · Jun 14th 2011

Re: Sorting by date formula with both numerical values and text values

Hi Glenn,

I've sent you an email with the password.

The sorting will be for all the months within the year. I just used May as an example. Currently the sorting works when the date is added numerically (e.g. 5/1/2011) but I want, when there is no exact date, to be able to put the words "Early May", Late May or "Mid May" and have that be sorted alphabetically all at the same time the other dats are being sorted numerically. That is why there is a type mismatch--becasue I havent got the right code to tell excel to sort the text dates while sorting the numeric dates.

Brand_New2VBA · Jun 14th 2011

Re: Sorting by date formula with both numerical values and text values

Hi,

Thank you so much for replying to my inquiry. I have attached a sample of my work below. I would like it to be sorted numerically first. e.g.:
5/1/2011
5/5/2011
5/6/2011
Early May
Late May
Mid May

Please see the attached file (below) named "Sample". Hopefully this will make things a bit clearer. I hope you can help me with this.

forum.ozgrid.com/index.php?attachment/38948/

Brand_New2VBA · Jun 14th 2011

Hi All,

I've come across a road block. The sorting code I have works well only with numerical figures (5/1/2011). How can I make it work with both text ( Early May) and numerical (5/1/2001)figures? for example, here what I have so far:

Code

Private Sub CommandButton2_Click()
Dim wks As Worksheet
Dim lngLastRowMay As Long, lngLastRowJune As Long, lngLastRowJuly As Long, lngLastRowAugust As Long, lngLastRowSeptember As Long, lngLastRowOctober As Long, lngLastRowNovember As Long, lngLastRowDecember As Long, lngActiveRow As Long
Dim intContainer As Integer, intRowIndex As Integer, intColumnIndex As Integer
Dim lLoop As Long, lLoop2 As Long
Dim varWorkflow() As String
Dim strCompanyReport1 As String, strType1 As String, strAnalyst1 As String, strSubmittedToSAs1 As String, strPublicationDate1 As String, strSA1 As String
Dim strCompanyReport2 As String, strType2 As String, strAnalyst2 As String, strSubmittedToSAs2 As String, strPublicationDate2 As String, strSA2 As String
    Set wks = Sheet1
    lngLastRowMay = wks.Range("B" & Rows.Count).End(xlUp).Row
 
    intContainer = (((lngLastRowMay - 6) - 4) / 8) + 1
    ReDim varWorkflow(1 To intContainer, 1 To 6)
 
    intRowIndex = 1
 
    For lngActiveRow = 5 To lngLastRowMay Step 8
 
        If wks.Range("C" & lngActiveRow).Value = vbNullString Then
            varWorkflow(intRowIndex, 1) = vbNullString
        Else
            varWorkflow(intRowIndex, 1) = CStr(wks.Range("C" & lngActiveRow).Value)
        End If
 
        If wks.Range("C" & lngActiveRow + 1).Value = vbNullString Then
            varWorkflow(intRowIndex, 2) = vbNullString
        Else
            varWorkflow(intRowIndex, 2) = CStr(wks.Range("C" & lngActiveRow + 1).Value)
        End If
 
        If wks.Range("C" & lngActiveRow + 2).Value = vbNullString Then
            varWorkflow(intRowIndex, 3) = vbNullString
        Else
            varWorkflow(intRowIndex, 3) = CStr(wks.Range("C" & lngActiveRow + 2).Value)
        End If
 
        If wks.Range("C" & lngActiveRow + 3).Value = vbNullString Then
            varWorkflow(intRowIndex, 4) = vbNullString
        Else
            varWorkflow(intRowIndex, 4) = Format(CDate(wks.Range("C" & lngActiveRow + 3).Value), "yyyy-mm-dd")
        End If
 
        If wks.Range("C" & lngActiveRow + 4).Value = vbNullString Then
            varWorkflow(intRowIndex, 5) = vbNullString
        Else
            varWorkflow(intRowIndex, 5) = Format(CDate(wks.Range("C" & lngActiveRow + 4).Value), "yyyy-mm-dd")
        End If
 
        If wks.Range("C" & lngActiveRow + 5).Value = vbNullString Then
            varWorkflow(intRowIndex, 6) = vbNullString
        Else
            varWorkflow(intRowIndex, 6) = CStr(wks.Range("C" & lngActiveRow + 5).Value)
        End If
 
 
        intRowIndex = intRowIndex + 1
 
    Next lngActiveRow
 
    Debug.Print "Sorting Dates"
 
 
    For lLoop = 1 To UBound(varWorkflow, 1)
        For lLoop2 = lLoop To UBound(varWorkflow, 1)
 
        If varWorkflow(lLoop2, 5) <> vbNullString Then
            If UCase(varWorkflow(lLoop2, 5)) < UCase(varWorkflow(lLoop, 5)) Then
                strCompanyReport1 = varWorkflow(lLoop, 1)
                strType1 = varWorkflow(lLoop, 2)
                strAnalyst1 = varWorkflow(lLoop, 3)
                strSubmittedToSAs1 = varWorkflow(lLoop, 4)
                strPublicationDate1 = varWorkflow(lLoop, 5)
                strSA1 = varWorkflow(lLoop, 6)
 
                strCompanyReport2 = varWorkflow(lLoop2, 1)
                strType2 = varWorkflow(lLoop2, 2)
                strAnalyst2 = varWorkflow(lLoop2, 3)
                strSubmittedToSAs2 = varWorkflow(lLoop2, 4)
                strPublicationDate2 = varWorkflow(lLoop2, 5)
                strSA2 = varWorkflow(lLoop2, 6)
 
                varWorkflow(lLoop, 1) = strCompanyReport2
                varWorkflow(lLoop, 2) = strType2
                varWorkflow(lLoop, 3) = strAnalyst2
                varWorkflow(lLoop, 4) = strSubmittedToSAs2
                varWorkflow(lLoop, 5) = strPublicationDate2
                varWorkflow(lLoop, 6) = strSA2
 
                varWorkflow(lLoop2, 1) = strCompanyReport1
                varWorkflow(lLoop2, 2) = strType1
                varWorkflow(lLoop2, 3) = strAnalyst1
                varWorkflow(lLoop2, 4) = strSubmittedToSAs1
                varWorkflow(lLoop2, 5) = strPublicationDate1
                varWorkflow(lLoop2, 6) = strSA1
 
            End If
        End If
        Next lLoop2
    Next lLoop
 
    Debug.Print "Sorted Dates"
    Debug.Print "Inputting New Dates"
 
    intRowIndex = 1
 
    For lngActiveRow = 5 To lngLastRowMay Step 8
 
        wks.Range("C" & lngActiveRow).Value = varWorkflow(intRowIndex, 1)
        wks.Range("C" & lngActiveRow + 1).Value = varWorkflow(intRowIndex, 2)
        wks.Range("C" & lngActiveRow + 2).Value = varWorkflow(intRowIndex, 3)
        wks.Range("C" & lngActiveRow + 3).Value = varWorkflow(intRowIndex, 4)
        wks.Range("C" & lngActiveRow + 4).Value = varWorkflow(intRowIndex, 5)
        wks.Range("C" & lngActiveRow + 5).Value = varWorkflow(intRowIndex, 6)
 
        intRowIndex = intRowIndex + 1
 
    Next lngActiveRow

Display More

Now I need to also tell excel to sort if the dates entered happen to be, for example, Early May, Mid May or Late May. How can I get the formula to tell excel that if a numerical date is entered the the specified cells ( 5/23/2011) sort date, however if only text is entered (Late May) sort alphabetically by that entry as well.
Can this be done? If so, how? Can you please help me with this? Thank you!

Brand_New2VBA · May 31st 2011

Re: Date recognition using IF formula

Hi Barb,

Thanks for the advice. I tried your codes and it didnt pan out very well for me (kept getting a "type mismatch error"). I also tried this and it doesnt work either:

Code

Dim wksSource As Worksheet
    Dim wksDestination As Worksheet
    Dim vntCloneValues As Variant
    Dim nextrow As Long
    Set wksSource = Sheet3
    Set wksDestination = Sheet1
    
    If Sheet3.Range("F11").Value >= "5/1/0000-5/31/0000" Then
    If Sheet1.Cells(Rows.Count, "c") = "" Then
    nextrow = Sheet1.Cells(Rows.Count, "C").End(xlUp).Row + 4
    If Sheet1.Cells(Rows.Count, "c") = "" Then
    nextrow = Sheet1.Cells(Rows.Count, "C").End(xlUp).Row + 3
    vntCloneValues = wksSource.Range("F7:F12").Value
    wksDestination.Range("C" & nextrow).Resize(UBound(vntCloneValues), 1) = vntCloneValues
    Erase vntCloneValues
     Sheet3.Range("F7:F12").ClearContents
     End If
     End If
     End If
end sub

Display More

this doesnt send only may dates to the may column ("C") it also send every other dat there too.

How to I isolate the dates?

Brand_New2VBA · May 26th 2011

Hi,

I keep hitting my head against the wall with this formula. I would the data being copied to paste in a certain column only if the information in the range containes the month May in it. I tried Dim May as date but it didnt work then I tried an IF formula telling excel to pick up the word May and I keep getting the error message type mismatch. This is what I have so far and I'm not getting anywhere with it.

Code

Dim wksSource As Worksheet
    Dim wksDestination As Worksheet
    Dim vntCloneValues As Variant
    Dim nextrow As Long
    
    
    Set wksSource = Sheet3
    Set wksDestination = Sheet1
    
    If Range("F7:F12").Value = "May" Then
    vntCloneValues = wksSource.Range("F7:F12").Value
    nextrow = Sheet1.Cells(Rows.Count, "C").End(xlUp).Row + 3
    wksDestination.Range("C" & nextrow).Resize(UBound(vntCloneValues), 1) = vntCloneValues
    Erase vntCloneValues
     Sheet3.Range("F7:F12").ClearContents
     End If
     
     
    End Sub

Display More

Can anyone help me complete this code? Am i way off or am I close?

Brand_New2VBA · May 25th 2011

Re: Execute code based on specific text in target range of cells

The code I have now executes the task its told--which is to copy and past information to column C. I'd like to completely remove that feature. Rather than tell it a specific column to copy and paste to I want the information to be pasted under whatever month is indicated in the "intended Publication date" in the appropriate column and spaces allocated for that particular data--please use spreadsheet attached for refeence (Sheet1 "Future workflow").

Brand_New2VBA · May 24th 2011

Re: Execute code based on specific text in target range of cells

Thanks so much for your help so far. Ed. You've been a very good teacher in helping me understand the methodology behind VBA coding.

Alright, I've gotten a bit further with this formula, now all I need to do is have the information being copied from "Sheet 3" to be pasted according to the Date range on Sheet 1. For instance:

information in a quese has teh following data
John Black
Doctor
May 23

I would like this information to be copied and pasted under the column designated for May dates under the last set of data without me having to indicate the specifice range. Is this possible? Please see the spreadsheet attached above (in the earlier posts) for examples.

Code

Dim wksDestination As Worksheet
    Dim vntCloneValues As Variant
    Dim nextrow As Long
    
   Set wksSource = "Sheet3"
    Set wksDestination = "Sheet1"
    
    If Sheet1.Cells(Rows.Count, "c") = "" Then
    nextrow = Sheet1.Cells(Rows.Count, "C").End(xlUp).Row + 4
    If Sheet1.Cells(Rows.Count, "c") = "" Then
    nextrow = Sheet1.Cells(Rows.Count, "C").End(xlUp).Row + 3
    vntCloneValues = wksSource.Range("F7:F12").Value
    wksDestination.Range("C" & nextrow).Resize(UBound(vntCloneValues), 1) = vntCloneValues
    Erase vntCloneValues
     Sheet3.Range("F7:F12").ClearContents
 
    If Sheet1.Cells(Rows.Count, "c") = "" Then
    nextrow = Sheet1.Cells(Rows.Count, "C").End(xlUp).Row + 4
    If Sheet1.Cells(Rows.Count, "c") = "" Then
    nextrow = Sheet1.Cells(Rows.Count, "C").End(xlUp).Row + 3
    vntCloneValues = wksSource.Range("F15:F20").Value
    wksDestination.Range("C" & nextrow).Resize(UBound(vntCloneValues), 1) = vntCloneValues
    Erase vntCloneValues
     Sheet3.Range("F15:F20").ClearContents
End Sub

Display More

Brand_New2VBA · May 23rd 2011

Re: Execute code based on specific text in target range of cells

Hi Ed,

Hope your weekend went well. Thanks for the advise. I tried you way, however I keep getting an error. This is what I'm using:

Code

Private Sub CommandButton1_Click()
    Dim wksSource As Worksheet
    Dim wksDestination As Worksheet
    Dim vntCloneRange As Variant
     Dim nextrow As Long
 
    Set wksSource = Sheet1
    Set wksDestination = Sheet4
 
     If Range("B4,e4") = "published" Then
    vntCloneRange = wksSource.Range("c5:c10")
    nextrow = Sheet4.Range("May").Rows.Count("C").End(xlUp).Row + 3
    wksDestination.Range("C" & nextrow).Resize(UBound(vntCloneRange), 1) = vntCloneRange
    Erase vntCloneRange
    Range("b4:e11").Delete Shift:=xlUp
End if
end sub

Display More

Where did I go wrong?

Posts by Brand_New2VBA

CLOSE out a USERFORM after commandbutton is clicked

MACRO that allows user to copy and insert multiple worksheets through userform comman

MACRO that allows user to copy and insert multiple worksheets through userform comman

MACRO that allows user to copy and insert multiple worksheets through userform comman

MACRO that allows user to copy and insert multiple worksheets through userform comman

MACRO that allows user to copy and insert multiple worksheets through userform comman

MACRO that allows user to copy and insert multiple worksheets through userform comman

MACRO that allows user to copy and insert multiple worksheets through userform comman

Vba code that automatically inserts new sheet after hitting comman button

Vba code that automatically inserts new sheet after hitting comman button

Sorting by date formula with both numerical values and text values

Sorting by date formula with both numerical values and text values

Sorting by date formula with both numerical values and text values

Sorting by date formula with both numerical values and text values

Sorting by date formula with both numerical values and text values

Date recognition using IF formula

Date recognition using IF formula

Execute code based on specific text in target range of cells

Execute code based on specific text in target range of cells

Execute code based on specific text in target range of cells