Macro/VBA help needed

TheCman

New member
Local time
Today, 20:14
Joined
Dec 20, 2011
Messages
3
Hi Guys,

The following code is what I use to copy rows from my excel report depending on the outcome of a particular colum to another sheet. However what I would like to know is instead of just copying the rows over, I want to move the rows completly out of the "Main" Sheet to the specified sheet.

Code as follows:

Dim Mfile As String
Dim Dsh As String
Dim Rend As Long
Dim Dd As Long
Dim Nend As Long

Dsh = "Main"
Mfile = ActiveWorkbook.Name

Workbooks(Mfile).Activate
Sheets(Dsh).Select
Rend = Cells(65536, 1).End(xlUp).Row

For Dd = 1 To Rend

Select Case Cells(Dd, 6)
Case "1"
Nend = Sheets("Missing1").Cells(65536, 1).End(xlUp).Row + 1
Rows(Dd).Copy Destination:=Sheets("Missing1").Cells(Nend, 1)

End Select
Next Dd

Sheets("Sheet2").Select
Columns("F:F").Select
Selection.ClearContents
Range("A1").Select

End Sub

Now this part of my code is used to copy the lines:

Select Case Cells(Dd, 6)
Case "1"
Nend = Sheets("Missing1").Cells(65536, 1).End(xlUp).Row + 1
Rows(Dd).Copy Destination:=Sheets("Missing1").Cells(Nend, 1)

But what I want to know is there away to change the "Copy Destination" to a move function?

Hope I have giving enough info.

Cheers

Colin
 
I'm not an excel person but my guess would be to carry on doing what you are already doing and then add a routine for deleting the items.
 
Thanks for the input. I thought about doing that, it's fairly simple to add another routine to remove them but I wanted to add something into what I already have to remove then in one step so to speak!
 
You're using the Copy method, there must be Cut and Paste (or PasteSpecial) methods too right?
 
I've manage to resolve my issue by using the following code:

Code:
[FONT=Calibri][SIZE=3]Sub testv2()[/SIZE][/FONT]
 
[FONT=Calibri][SIZE=3]x = Sheets("Sheet2").Range("I" & Rows.Count).End(xlUp).Row[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]For Each cell In Sheets("Sheet2").Range("I1:I" & x)[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]If cell = 1 Then[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]y = WorksheetFunction.CountA(Sheets("Sheet1").Columns(1))[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Rows(cell.Row).Copy Destination:=Sheets("Sheet1").Range("a" & y + 1)[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]cell.ClearContents[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]End If[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Next cell[/SIZE][/FONT]
 
[FONT=Calibri][SIZE=3]Sheets("Sheet2").Columns(9).SpecialCells(xlCellTypeBlanks).EntireRow.Delete[/SIZE][/FONT]
 
 
[FONT=Calibri][SIZE=3]End Sub[/SIZE][/FONT]

:)
 
Good to see that you found your own solution... but you're doing double work. The Range() collection has a Cut method. Think about how the Cut and Paste function works.
 

Users who are viewing this thread

Back
Top Bottom