Posts by hgus393

    Re: Copy and paste without clipboard to another book


    What about something like this - untested though


    Code
    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


    Robert

    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?


    Robert

    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:



    Robert

    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..



    :thanx:
    Would using

    Code
    For lngIndex = 1 To Worksheets.Count


    Be a good option for all the worksheets?
    /Robert

    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:


    Robert

    Re: Creating an automated database


    Hi,


    Knicked some of Joebbshop code to solve the problem:


    Code
    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!


    /Robert

    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.

    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:
    Robert

    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?



    :thanx:
    Robert

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


    Hi,


    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..



    Robert

    Re: Keep hyperlink when pasting as value?


    Quote from Obelix

    Thanx,
    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!