too tricky?

Posted by Mark on April 08, 2001 3:32 AM

on sheet one of my workbook,I have a list of various details(customer,quantity,order date....).What I would like to do is copy the last line of data from this sheet onto the next available line on sheet 2,I was hoping that I could do this with a controll button placed onto sheet one.
also(just to make things even more complicated!)on sheet 2,I have a column that is filled in with "yes" if the work for that line has been completed(blank if it is still in progress)What I would like on this one is,if the completed column has "yes" typed into it,then the whole line deleted,and any gaps between the rows removed(contents below moved up)
is this possible or am i expecting too much?!

thankyou in advance
Mark

Posted by Dave Hawley on April 08, 2001 4:21 AM

Hi Mark

1. Push Alt+F11 and paste in the code below.

2. Change "Sheet1" and "Sheet2" to suit.

3. Change "B:B" to the column on sheet2 that has Yes or blank.

4. Push Alt+Q

5.Go to View>Toolbars>Forms and select a CommandButton.

6.Place it on any worksheet. When the "Assign Macro" box appears, select "CopyIt" then "Ok"

7. Save and click your button.

'The code:

Sub CopyIt()
'Written by OzGrid Business Applications.
'Website www.ozgrid.com
Dim Wsht1 As Worksheet
Dim Wsht2 As Worksheet

Set Wsht1 = Sheets("Sheet1") 'Copy from sheet
Set Wsht2 = Sheets("Sheet2") 'Copy to sheet

'Find the last cell in Column A and copy the entire row _
to the row below the last entry on the "copy to sheet"
Wsht1.Range("A65536").End(xlUp).EntireRow.Copy _
Destination:=Wsht2.Range("A65536").End(xlUp).Offset(1, 0)

'Clear the clipboard
Application.CutCopyMode = False

'Delete the entire row of all blank cells in Column B
Wsht2.Columns("B:B").SpecialCells(xlBlanks).EntireRow.Delete

Set Wsht1 = Nothing
Set Wsht2 = Nothing
End Sub

Dave

OzGrid Business Applications

Posted by Mark on April 08, 2001 7:04 AM

almost works

'The code: Sub CopyIt() 'Written by OzGrid Business Applications. 'Website www.ozgrid.com Dim Wsht1 As Worksheet Dim Wsht2 As Worksheet Set Wsht1 = Sheets("Sheet1") 'Copy from sheet Set Wsht2 = Sheets("Sheet2") 'Copy to sheet 'Find the last cell in Column A and copy the entire row _ to the row below the last entry on the "copy to sheet" Wsht1.Range("A65536").End(xlUp).EntireRow.Copy _ Destination:=Wsht2.Range("A65536").End(xlUp).Offset(1, 0) 'Clear the clipboard Application.CutCopyMode = False 'Delete the entire row of all blank cells in Column B Wsht2.Columns("B:B").SpecialCells(xlBlanks).EntireRow.Delete Set Wsht1 = Nothing Set Wsht2 = Nothing End Sub

Dave

hello dave,

Your solution to my question seems to work ok for the first part of the problem(copy the last line of sheet 1 to the next available line of sheet 2)
but the second part doesnt(if "yes" is entered into the completed column,delete that entire line)

any ideas?

ps.i changed the "B:B" reference to "E:E" as you said,also i get an error window that gives 400

thanks Mark

Posted by Dave Hawley on April 08, 2001 7:46 PM

Re: almost works

Hi Mark

Sorry, I had it back to front, I thought you wanted all rows WITHOUT yes in them deleted. I have changed it now to delete all rows WITH yes.

The code is assuming all cells with "Yes" in them are simply typed text and not the result of a formula, in other words the cells with yes DO NOT have a formula in them. If they do let me know and I'll modify it.

Sub CopyIt()
'Written by OzGrid Business Applications.
'Website www.ozgrid.com
Dim Wsht1 As Worksheet
Dim Wsht2 As Worksheet

Set Wsht1 = Sheets("Sheet1") 'Copy from sheet
Set Wsht2 = Sheets("Sheet2") 'Copy to sheet

'Clear the clipboard
Application.CutCopyMode = False

'Delete the entire row of all blank cells in Column E
Wsht2.Columns("E:E").SpecialCells _
(xlCellTypeConstants, xlTextValues).EntireRow.Delete

Set Wsht1 = Nothing
Set Wsht2 = Nothing
End Sub

OzGrid Business Applications

Posted by mark on April 09, 2001 10:59 AM

Re: almost works

dave,
thanks for the reply,this kind of works(good enough for me)what i did was control the delete part of the code with a comand button
i dont know if this is the right thing to do,but it works,
the only problem i have now is stopping it deleting too much - it deletes the column headings as well is there a way around this?

cheers,mark

Posted by Dave Hawley on April 09, 2001 9:52 PM

Re: almost works

thanks for the reply,this kind of works(good enough for me)what i did was control the delete part of the code with a comand button i dont know if this is the right thing to do,but it works, the only problem i have now is stopping it deleting too much - it deletes the column headings as well is there a way around this? cheers,mark

Hi Mark

Just change:
"Wsht2.Columns("E:E").SpecialCells _
(xlCellTypeConstants, xlTextValues).EntireRow.Delete"

Wsht2.Range("E2:E10000").SpecialCells _
(xlCellTypeConstants, xlTextValues).EntireRow.Delete

Dave

OzGrid Business Applications