Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Macro/VBA code help


(!)

TheCman's Avatar
TheCman TheCman is offline
Computer Specs
Junior Member with 5 posts.
THREAD STARTER
 
Join Date: Dec 2011
Experience: Advanced
22-Dec-2011, 06:52 AM #1
Smile Macro/VBA code help
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
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
22-Dec-2011, 10:07 AM #2
I wouldn't recommend junking "source" rows as you go, that's likely to "disorientate" your loop. If you do it that way you're supposed to loop from bottom to top, or something.

Instead I'd blank each column 1 cell in the rows to be moved and purge them at the end in one hit. Something like:

Sub test()
x = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
For Each Cell In Sheets("Sheet2").Range("A1:A" & x)
If Cell = "A" Then
y = WorksheetFunction.CountA(Sheets("Sheet1").Columns(1))
Rows(Cell.Row).Copy Destination:=Sheets("Sheet1").Range("A" & y + 1)
Cell.ClearContents
End If
Next Cell

Sheets("Sheet2").Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub


HTH
TheCman's Avatar
TheCman TheCman is offline
Computer Specs
Junior Member with 5 posts.
THREAD STARTER
 
Join Date: Dec 2011
Experience: Advanced
22-Dec-2011, 10:23 AM #3
Hi Bomb,

The following part of that code shows a runtime error:

Sheets("Sheet2").Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Rollin_Again's Avatar
Member with 4,702 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
22-Dec-2011, 10:28 AM #4
Try using .Cut Destination instead of .Copy Destination


Regards,
Rollin
TheCman's Avatar
TheCman TheCman is offline
Computer Specs
Junior Member with 5 posts.
THREAD STARTER
 
Join Date: Dec 2011
Experience: Advanced
22-Dec-2011, 10:41 AM #5
Hi Rolllin,

Thanks Cut Destination works but leaves the blank line on the Main tab

Think I can sort something out to remove the blank rows
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
22-Dec-2011, 10:41 AM #6
Quote:
Originally Posted by TheCman View Post
Hi Bomb,

The following part of that code shows a runtime error:

Sheets("Sheet2").Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Not when I run it as per attached. Maybe there's something different with your environment. I never post code without giving it a run first.

ETA: do you perhaps have 10s of 1000s of rows? http://support.microsoft.com/kb/832293
Attached Files
File Type: xls Book2.xls (16.0 KB, 30 views)

Last edited by bomb #21; 22-Dec-2011 at 10:48 AM..
TheCman's Avatar
TheCman TheCman is offline
Computer Specs
Junior Member with 5 posts.
THREAD STARTER
 
Join Date: Dec 2011
Experience: Advanced
22-Dec-2011, 11:12 AM #7
Hi Bomb

I've ran yours and it works based on "A" being in the columns. But doesnt work with mine! I only have a few hundered rows.

To make it a bit easier to understand, once I do my lookup and if the value "1" is returned in column "I" then move those lines.
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
22-Dec-2011, 12:37 PM #8
"if the value "1" is returned in column "I" then move those lines."

Modified to satisfy that works too (attached).
Attached Files
File Type: xls Book2.xls (17.0 KB, 33 views)
TheCman's Avatar
TheCman TheCman is offline
Computer Specs
Junior Member with 5 posts.
THREAD STARTER
 
Join Date: Dec 2011
Experience: Advanced
23-Dec-2011, 04:52 AM #9
Thanks dude, works liek a dream
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


Tags
macro, move copy row, vba

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑

Content Relevant URLs by vBSEO 3.3.2