1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Macro/VBA code help

Discussion in 'Business Applications' started by TheCman, Dec 22, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. TheCman

    TheCman Thread Starter

    Joined:
    Dec 22, 2011
    Messages:
    5
    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
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,271
    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. :confused:

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

    TheCman Thread Starter

    Joined:
    Dec 22, 2011
    Messages:
    5
    Hi Bomb,

    The following part of that code shows a runtime error:

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

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,732
    Try using .Cut Destination instead of .Copy Destination


    Regards,
    Rollin
     
  5. TheCman

    TheCman Thread Starter

    Joined:
    Dec 22, 2011
    Messages:
    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
     
  6. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,271
    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:

  7. TheCman

    TheCman Thread Starter

    Joined:
    Dec 22, 2011
    Messages:
    5
    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.
     
  8. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,271
    "if the value "1" is returned in column "I" then move those lines."

    Modified to satisfy that works too (attached).
     

    Attached Files:

  9. TheCman

    TheCman Thread Starter

    Joined:
    Dec 22, 2011
    Messages:
    5
    Thanks dude, works liek a dream :)
     
  10. Sponsor

As Seen On
As Seen On...

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1032408