Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 32

Thread: New problem (AutoCopying)

  1. #1

    New problem (AutoCopying)

    Hi everyone. Got promoted so have to find away to set up a Duplicate checking system for the new person taking my place. I am looking to auto copy new data into an ongoing list that will be stored in the same workbook. The data is entered onto the worksheet "List" and i need it to be copied into the worksheet "paid".

    The two worksheet have the same columns and info. All i want is for the info to be updated with the new entries from the "list" sheet. The workbook will be saved as Default and then saved again using a VBA supplied by you all earlier that saveas with the day's date. Not too worried about the saveas with the date. can do that manually.

    so here we go.

    Starting on B8 and working towards L8 information is entered. The next invoice's info is then entered on B9 - L9 and rhinse and repeat till all invoices are entered. Upon finishing the entries I need the following things taken care of for me.

    1. in column M is a vlookup command that compares the statement # that is located in column D against all statement #s in Column D on the paid sheet. I would like to change the values (they are sum commands to start with) in columns K & L to zero if the value in M is anything other than the default N/A# that is returned when vlookup doesn't find a match.

    2. I need to have all the new enteries copied and pasted into the Paid sheet starting with the next available row. This info needs to be copied so that the formulas that are in columns E, F, J, K & L are pasted with the Values. ***** ROW M IS NOT TO BE COPIED TO THE PAID SHEET ******

    3. Lastly I need to have the wookbook save itself.

    On that note ( the saving) I need a string that will clear the cells B,C,D,E,H & I of all info at the opening of the workbook. This is so that i do not have to erase the info from the save at the end of the copying VBA code asked for above. INFO CANNOT BE ERASED AFTER COPYING AS IT NEEDS TO BE COPIED WITH A SAVEAS WITH DATE COMMAND.

    sound confusing? i hope not got yelled at last time I asked for something cause of lack o' info.

    here is a practice sheet. Ignore sheets 2 & 3 they are left there because the actual forms have two sheets between the "list" & "paid" sheets.

  2. #2
    VBAX Regular
    Joined
    Jul 2004
    Location
    Wellington, New Zealand
    Posts
    37
    Location

    Nobody's Replies So...

    Hi, its great that you have put so much effort in detailing what you got. Unfortunately large volumes of text tend to put people off (well me anyway) so IMHO its better to put your post into smaller chunks as some of your questions seem to relate to formulas and others to a VBA solution.

    Personally, I havent got time at the moment to look through all this but if you have a go at using the macro recorder to do some things then ask for help on specifics you may get a better response.

    regards
    Parry.

  3. #3
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    1) There is no Vlookup in M? But to change the values try something like:

    Option Explicit
    Sub ChangeValues()
    Dim x               As Long
    Dim LastRow         As Long
        With Sheets("List")
            LastRow = .Range("A65536").End(xlUp).Row
            For x = 8 To LastRow
                If Application.WorksheetFunction.IsNA(.Range("M" & x).Value) Then
                    Range("K" & x & ":L" & x).Value = 0
                End If
            Next
        End With
    End Sub

  4. #4
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    2) Try something like this to copy the data over:


    Option Explicit
    Sub CopyIt()
    Dim NextRow As Long
    Dim LastRow As Long
        NextRow = Sheets("Paid").Range("A65536").End(xlUp).Row + 1
        LastRow = Sheets("List").Range("A65536").End(xlUp).Row
    Sheets("List").Range("A8:L" & LastRow).Copy
        With Sheets("Paid").Range("A" & NextRow)
            .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End With
    End Sub

  5. #5
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    3) To clear the contects on open try:


    Option Explicit
    Private Sub Workbook_Open()
        Sheets("List").Range("B8:I65536").ClearContents
    End Sub
    And you already have the SaveAs code right?

    If I missed something let me know.

  6. #6
    I apoligize for not adding the Vlookup string. I will try your solution out against a SS with the formula in it. SHould it not work I will attach a SS with formula to make it easier for you to test the problem.

  7. #7
    DRJ,

    Thanks for the reply I appreciate the work you have put inot the solution thus far. I tried running them and they have brought back

    Runtime Error '9':
    Subscript out of Range

    Debug shows a highlighted command of

    Change value VBA highlight of :

    With sheet ("list")
    Copy it VBA highlight of:

    LAstrow=sheet ("list"), range ("A65536"), end(x1up).row
    On opening the form the delete rows VBA does not delete any of the rows data. I am attaching a new spreadsheet with all the sheets properly named and all formulas in there correct cells. Ihave some data punched into non formula cells to simulate the usage and allow you to test your solutions to see what I have done worng.

    BTW in your code


    Option Explicit 
    Private Sub Workbook_Open() 
        Sheets("List").Range("B8:I65536").ClearContents 
    End Sub

    The Range of cells cannot include any info in Columns F or G. I realized a mistake in explaination also. The data needs to be cleared only on certain forms so I would need excel to ask if I want to clear the info. If not then I need the info to stay. This allows me to go back in to the spreadsheet and continue working on it.

    I was wondering if you could add the following:

    In cell B2 is the date the spreadsheet is created. Can we have that cell depopulated upon opening the worksheet and then repopulated with the days date? This would also be part of the above request to have it ask if I want the date to change. You can bundle this with the clear data command as the only time the date would change would be for a new billing period.

    See attachment

    Thanks a ton for the help

  8. #8
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    If you just want todays date how about the

    =Today()
    formula for the cell?


    If the names of the sheets are different, then you will get an out of range error, but just change the code to refer to the correct name.

    If you want to be asked to delete the data try something like:


    Option Explicit
    Private Sub Workbook_Open()
    Dim MyResponse As VbMsgBoxResult
        MyResponse = MsgBox("Do you want to clear the cells?", vbYesNo + vbQuestion)
        If MyResponse = vbYes Then
            Sheets("List").Range("B8:I65536").ClearContents
        End If
    End Sub

  9. #9
    Won't the =today() change the date each time the sheet is opened?

  10. #10
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    The Today() function will change everytime your settings change a day and Excel calculates. So not necessarily everytime it opens, but everyday, yes.

  11. #11
    Ok so i should manually change it or is there a way to have the VBA code that asks to clear the content can have the date put in.

    The only time the date will change is when i want to clear the content and start a new sheet so if the same question was to change the date that would be great.

    should i add something like

    B2=today() to the code?

    Also THANK YOU everything is working great !!!!

    except the change value based on M is working in the opposite direction. Currently as typed it is changing any value in K & L to $0 if the vlookup in M is bringing back a value of #N/A. I need it to change the value if the command brings back any value OTHER than #N/A.

    So I would need to change that to what?

    For those who are getting involved that may have an answer to that fix here is the original code

    Sub ChangeValues()
        Dim x               As Long
        Dim LastRow         As Long
        With Sheets("List")
            LastRow = .Range("A65536").End(xlUp).Row
            For x = 8 To LastRow
                If Application.WorksheetFunction.IsNA(.Range("M" & x).Value) Then
                    Range("K" & x & ":L" & x).Value = 0
                End If
            Next
        End With
    End Sub

  12. #12
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Well this should clear all except F & G ...

    Option Explicit
    Private Sub Workbook_Open()
        Dim MyResponse As VbMsgBoxResult
        MyResponse = MsgBox("Do you want to clear the cells?", vbYesNo + vbQuestion)
        If MyResponse = vbYes Then
            Sheets("List").Range("B8:E65536,H8:I65536").ClearContents
        End If
    End Sub
    And you're earlier 'Subscript out of range' error was coming from your code. It was referring to sheet 'List' and your actual sheet name is 'Lists' with an s. And if you want to put your 0 in there if there is not an #N/A error, just try adding Not to the frong of your statement ...

    Option Explicit
    Sub ChangeValues()
        Dim x               As Long
        Dim LastRow         As Long
        With Sheets("Lists")
            LastRow = .Range("A65536").End(xlUp).Row
            For x = 8 To LastRow
                If Not Application.WorksheetFunction.IsNA(.Range("M" & x).Value) Then
                    Range("K" & x & ":L" & x).Value = 0
                End If
            Next
        End With
    End Sub
    That won't do anything for the values adjacent (same row) of your #N/A values, but zero out everything else. Is that what you're talking about?

  13. #13
    I actually realized the name problem and fixed it.

    The other WORKED !!! you guys are the best. thanks for helping me save my company tons O' money.

    One last request can you make a code or macro that will run other macros? I would like to make a button to run other macros but not all of them.

  14. #14
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Well hey, if you ever want to shovel some of that savings our way, we do PayPal.

    Really, glad it helped. So everything works well for you here?

  15. #15
    Argh! just when you think you are done.


    Sub CopyIt()
    Dim NextRow As Long
    Dim LastRow As Long
    NextRow = Sheets("Paid").Range("C65536").End(xlUp).Row + 1
    LastRow = Sheets("List").Range("C65536").End(xlUp).Row
    Sheets("List").Range("B8:L" & LastRow).Copy
    With Sheets("Paid").Range("B" & NextRow)
    .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End With
    End Sub

    In the above reference code how would i add Having the value present found in B2 in the 'LIST' page copied into the M column adjacent to all copied entries?

    In other words after copying the appropriate data from the 'LIST' page to the 'PAID' page I need to have the 'LIST B2' value copied into the ' PAID M' cells that correspond to the newly pasted data.

    Also I never got a reply on adding an auto date for the 'LIST B2' cell to the

    Sub Workbook_Open()
    Dim MyResponse As VbMsgBoxResult
    MyResponse = MsgBox("Do you want to clear the cells?", vbYesNo + vbQuestion)
    If MyResponse = vbYes Then
    Sheets("List").Range("B8:E65536").ClearContents
    Sheets("List").Range("H8:I65536").ClearContents
    End If
    End Sub
    Any further help would be appriciated on those two matters.


    New question same workbook.

    My boss has asked me to Idiot prove the insertion of extra data onto these forms.

    I currently have room for 25 seperate invoices from rows 8 - 32. Data is entered into those rows from Columns B - M. The previously attached Spreadsheet is a copy of the default form.

    If there are more or less than 25 invoices, my boss would like to have a macro or VBA that can be run that will add or subtract rows. Basically i manually put them in and simply highlight and insert then copy down the cells. Since i have been promoted and they are not sure of the experience of the replacement coming in he doesn't want me running around showing them how to maintain the Spreadsheet.

    SO .....

    I need a macro/VBA code that will do the folloing:

    Insert rows between the last current row (32) and the totals row (34) that will contain all formulas and also maintain an ongoing count of invoice (already present in Row A just needs to continue)

    and a seperate one that :

    Does the opposite. Deletes excess rows

    Ummmm while typing this edit someone pointed out that my little auto change of the values when M brings back a value different from #N/A removes the formula. Soooooo, When I use the clear code to remove the info in B-E and H & I the formulas have changed is there anyway to Clear those cells and populate the cells K8:L with the formulas that were removed?

    Just in case you need to know the formulas:

    in K it is =sum(H#*G#) same row

    in L it is =sum(L#*G#)

    so any help on these matters is greatly appreciated.

  16. #16
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    For the original question:

    For your open procedure, maybe something like this ...

    [vba]Option Explicit
    Private Sub Workbook_Open()
    Dim MyResponse As VbMsgBoxResult
    MyResponse = MsgBox("Do you want to clear the cells?", vbYesNo + vbQuestion)
    If MyResponse = vbYes Then
    Sheets("List").Range("B8:E65536").ClearContents
    Sheets("List").Range("H8:I65536").ClearContents
    Sheets("List").Range("B2").Formula = "=TODAY()"
    End If
    End Sub[/vba]

    As for putting the date next to the range in CopyIt maybe ...

    [vba]Option Explicit
    Sub CopyIt()
    Dim NextRow As Long
    Dim LastRow As Long
    NextRow = Sheets("Paid").Range("A65536").End(xlUp).Row + 1
    LastRow = Sheets("List").Range("A65536").End(xlUp).Row

    Sheets("List").Range("A8:L" & LastRow).Copy
    With Sheets("Paid").Range("A" & NextRow)
    .PasteSpecial Paste:=xlPasteValues
    End With
    With Sheets("Paid")
    .Range("M" & NextRow & ":M" & Range("L65536").End(xlUp).Row).Value = _
    Sheets("List").Range("B2").Value
    End With
    End Sub[/vba]

  17. #17
    OK go both worked.....

    The Copy code only entered the date on the first line of the new rows. Need it to copy down to the last row in the line.

    The clear button worked but how would i add a copy and paste special values command to it so that it takes the =today() formula out and leaves the date?


    that and the add and delete lines and this damn sheet will be done.

  18. #18
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    [vba]
    With Sheets("List")
    .Range("B2").Formula = "=TODAY()"
    .[B2].Value = .[B2].Value
    End With[/vba]

    And you want to keep those formulas in K & L, right?

  19. #19
    You guys are awesome!!!!!!!!!!!!!!!!!!!

  20. #20
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Okay, so when you start this process with your sheet (well, when the new guy does), is the total row already going to be there? I'm confused as to what you mean by deleting/inserting rows. Can you explain the background on that?

    Btw, your SUM(H#*G#*) formula can just be H#*G#, you don't need to sum it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •