PDA

View Full Version : New problem (AutoCopying)



rcbricker
08-02-2004, 11:03 AM
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.

parry
08-04-2004, 08:58 PM
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.

Jacob Hilderbrand
08-04-2004, 09:59 PM
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

Jacob Hilderbrand
08-04-2004, 10:04 PM
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

Jacob Hilderbrand
08-04-2004, 10:07 PM
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.

rcbricker
08-05-2004, 05:22 AM
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.

rcbricker
08-05-2004, 06:11 AM
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

Jacob Hilderbrand
08-05-2004, 07:10 AM
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

rcbricker
08-05-2004, 08:03 AM
Won't the =today() change the date each time the sheet is opened?

Zack Barresse
08-05-2004, 08:12 AM
The Today() function will change everytime your settings change a day and Excel calculates. So not necessarily everytime it opens, but everyday, yes.

rcbricker
08-05-2004, 08:29 AM
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

Zack Barresse
08-05-2004, 08:58 AM
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?

rcbricker
08-05-2004, 09:02 AM
I actually realized the name problem and fixed it.

The other WORKED !!! you guys are the best.:hi: 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.

Zack Barresse
08-05-2004, 09:03 AM
Well hey, if you ever want to shovel some of that savings our way, we do PayPal. :D

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

rcbricker
08-05-2004, 09:22 AM
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.:help

Zack Barresse
08-05-2004, 09:55 AM
For the original question:

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

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

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

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

rcbricker
08-05-2004, 10:04 AM
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.

Zack Barresse
08-05-2004, 10:13 AM
With Sheets("List")
.Range("B2").Formula = "=TODAY()"
.[B2].Value = .[B2].Value
End With

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

rcbricker
08-05-2004, 10:20 AM
You guys are awesome!!!!!!!!!!!!!!!!!!!

Zack Barresse
08-05-2004, 10:45 AM
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.

rcbricker
08-05-2004, 11:39 AM
Yes the row with the totals for columns H I & J will be there it is where they get the totals for payment.

I just need to be able to add more rows and/or delete them while carrying the information in columns A:M (only the default info not any entered data from invoices) with the insertion or deletion.

do you need a sample of the work sheet?

Zack Barresse
08-05-2004, 12:13 PM
...do you need a sample of the work sheet?

That may help.

rcbricker
08-05-2004, 12:28 PM
k

Anne Troy
08-08-2004, 11:14 AM
bump?

rcbricker
08-09-2004, 05:12 AM
Hiya dream!:p

what is bump? Thought it was duck then i say duck then fire says goose. J/K

Zack Barresse
08-09-2004, 07:02 AM
A bump will do just that, 'bump' the thread to the top of the forum, so other's will see it more easily. If your question sits for a while and doesn't get an answer sometimes people bump them. Just don't bump them too much. ;)



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

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


This is the part I don't understand. How do you want to guage how many rows are there? Does this mean that you want to input all the invoice stuff and then add the totals row by macro only? Just need to explain this part a little more, I'm catching up with Shades as I'm slow and confunsed. :yes

rcbricker
08-09-2004, 08:00 AM
I had not considered just adding the totals row by macro. That might work well. The problem is the formulas. But we can work on adding the totals row by macro.

SO lets try this.

A code to auto copy down the rows in sets of lets say 50 rows. Then once finished entering invoices have a code to remove all the rows that are not used to track invoice info. Then a macro to add the totals row one row below the last invoice row.

Sound good?

Zack Barresse
08-09-2004, 01:48 PM
So what will set apart the rows that you want deleted? Whether it has a Claim ID? You'll need to specify what to base that off of.

But in regards to a macro which adds the total of rows, maybe you could use something like this ...

Option Explicit
Sub rcbrickerTest()
Dim total As Range
Set total = Sheets("List").Range("G65536").End(xlUp).Offset(2)
If total.Offset(-2).Text = "TOTALS:" Then GoTo skip1
total.Value = "TOTALS:"
total.Offset(, 3).Formula = "=SUM(" & total.Offset(-2, 3).Address & ":$J$8)"
total.Offset(, 4).Formula = "=SUM(" & total.Offset(-2, 4).Address & ":$K$8)"
total.Offset(, 5).Formula = "=SUM(" & total.Offset(-2, 5).Address & ":$L$8)"
Exit Sub
skip1:
total.Offset(-2, 3).Formula = "=SUM(" & total.Offset(-4, 3).Address & ":$J$8)"
total.Offset(-2, 4).Formula = "=SUM(" & total.Offset(-4, 4).Address & ":$K$8)"
total.Offset(-2, 5).Formula = "=SUM(" & total.Offset(-4, 5).Address & ":$L$8)"
End Sub

This will check and see if a "TOTALS:" already exists and adjust accordingly to the SUM functions in columns J, K and L. Is that something like what your looking for in that regard?

rcbricker
08-13-2004, 05:57 AM
sorry it took so long to answer your question Fire.

Any data in Columns B-E would set a row apart from deletion. The one after the last row with data in these columns on down to the last row with a number in column A should be deleted. Hope this helps.

rcbricker
08-18-2004, 01:01 PM
Would it be possible to readdress this adding and subtracting rows?

Here are the questions again.

Adding

I need a code that will add rows in sets of 25 to the end of the last populated row (minus the totals) Including all formulas and in Column A a running total of how many rows are beiung used.

Deleting.

Just the opposite except that the rows are deleted based on the last row with information in any of Colmun B-E.

There is an attachment. Sheet affected is 'list' although sheet 'Paid" could benefit also.

Zack Barresse
08-18-2004, 01:32 PM
Maybe you could try something like this ... (??)

Sub insert25()
Dim newLoc As Long
newLoc = Range("A65536").End(xlUp).Row
Range("A" & newLoc & ":A" & newLoc + 25).EntireRow.Insert shift:=xlDown
End Sub
Sub delete25()
Dim newLoc As Long
newLoc = Range("A65536").End(xlUp).Row
Range("A" & newLoc & ":A" & newLoc + 25).EntireRow.Delete shift:=xlUp
End Sub

Zack Barresse
08-18-2004, 01:47 PM
Upon further review, this may work better for you. And it has an autofill ...

Sub insert25()
Dim nRow As Long
nRow = Range("A65536").End(xlUp).Offset(1).Row
Range("A" & nRow & ":A" & nRow + 24).EntireRow.Insert shift:=xlDown
Range("A" & nRow - 1 & ":M" & nRow - 1).AutoFill _
Destination:=Range("A" & nRow - 1 & ":M" & nRow + 24), Type:=xlFillDefault
End Sub
Sub delete25()
Dim nRow As Range
Set nRow = Range("A65536").End(xlUp)
Range("A" & nRow.Row & ":A" & nRow.Row - 24).EntireRow.Delete shift:=xlUp
End Sub