Posts by hgus393

    Re: Copy and paste without clipboard to another book

    What about something like this - untested though

    Sub Copy()
    Dim RngCopy As Range
    Dim RngPaste As Range
    Set RngCopy = Workbooks("Your book.xls").Sheets("Sheet1").Range("A1:A200")
    Set RngPaste = Workbooks("Book to paste.xls").Sheets("Sheet1").Range("A1:A200")
    RngCopy.Copy RngPaste
    End Sub


    Re: creating your own VBA code

    Quote from ShubhamIND

    I want to make a data bank of my own in excel under diiferent headings like Company Name, Name, Address,City,State,PIN, Country,Phone,Fax etc.
    I want a sheet where different input boxes pop-up and when the user enters the details they keep getting recorded under the headings.PLEASE help!!!

    Hi ShubhamIND and welcome!
    Could you please show us how far you have gotten? I.e. attach an example?


    Re: To make 'Save' Button to actually save the worksheet using VBA

    Hello Statsboi,

    Your VBA project is password locked as such I cannot see your code. However, I wrote some code that will probably do what you want, don't know if it is the best way to do this, someone else might have a good idea?
    Here goes:


    Re: Send a mail automatically

    Quote from Baskent

    I have a worksheet in local area network of which some of the cells should be filled by my colleagues when the information is received. But, generally my colleagues are forgetting to do. In the worksheet there is a column where their initials exists. What I required, if possible, to remind them if the cells were not filled within given days. This mail should be sent to the person with initials in the given row and to me.

    Thank you for your assistance in advance.

    Could you send an example workbook?

    Re: Msgbox VBYesNo and then what..

    Would using

    For lngIndex = 1 To Worksheets.Count

    Be a good option for all the worksheets?

    Hello all,

    I have a little problem that I hope someone can solve. I have a macro that finds multiple occurences on every sheet in a workbook by using the countif function. If the macro finds a multiple occurence it will colour that cell red and display a message that explains that there are multiple occurences and if the user wants to continue. I have worked out the No part, but what I would like is if the users presses yes then it will ignore all the multiple occurences that exits in all the worksheets. Does anybody know how to do this?
    Here my code:


    Re: Creating an automated database


    Knicked some of Joebbshop code to solve the problem:

    Sub TryThisNow()
        Dim i As Integer
        Dim Irow As Integer
        Irow = 0
        For i = 0 To 9999
            Irow = i
            Cells(10 + i, 2) = Irow
        Next i
    End Sub

    Hope this helps!


    Re: Removing duplicate cells - macro runs slow

    Quote from thomach

    re your code, you are going to extra work writing the formulas into column C. These formulas (all that remain) also need to recalculate after each row is deleted. Have a look at this code.

    Sub DownSelect()
    Dim r As Long, LstRow As Long
    LstRow = Range("B" & Rows.Count).End(xlUp).Row
    For r = LstRow To 2 Step -1
        If WorksheetFunction.CountIf(Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row), Cells(r, 2)) > 1 _
            Then Rows(r).EntireRow.Delete
    Next r
    End Sub

    :yourock: This was an approach I never would have thought of! :thanx: :thanx: :thanx:

    Hello all,

    I have account numbers in a column B, there might be more than one occurence in the column. What I am trying to do is to remove these multiple occurences by a countif formula and if the of the countif value is >= 2 then the cell should be deleted. However the macro runs reaaallllly slow. Can anybody see what I am doing wrong?


    Re: Create a "save button" on xls using VBA


    I could not enter your vba project area as it was password protected but I have taken a quick and dirty approach that works - I guess it would be better with a workbook event ie before close or before save but the skills fail me..


    Re: Keep hyperlink when pasting as value?

    Quote from Obelix

    but now I get this message: sorry, but I don't know how that would translate into english.

    Translated to English = Compilation Error, undefined variable...

    By the way the macro works fine for me!