Results 1 to 4 of 4

Thread: Macro that copies data and inserts it into another worksheet

  1. #1

    Macro that copies data and inserts it into another worksheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a excel workbook with several sheets, These sheets get deleted by a macro that I created but before I delete them I want to copy a range of cells and insert them into another sheet that will keep data for six months or longer. I have a macro started and it works for what I want but it also copies the cells that are left blank and also inserts them. Here is what I have so far, all I need is when it copies the range the blank ones are left out. Any help would be greatly appreciated.

    Thank you in advance

    Sub PostData()

    Dim CopyRange As Range, NextCell As Range

    Set CopyRange = Sheet2.Range("D625")
    Set NextCell = Sheet5.Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0)

    CopyRange.Copy
    NextCell.Insert Shift:=xlDown
    Application.CutCopyMode = False

    End Sub

  2. #2
    Are the empty cells interspersed within the range (makes the job complex), or a contiguous set of cells at the end of the range (much easier)?

  3. #3
    The empty cells are at the end of the range.

    Sent from my ADR6325 using Tapatalk

  4. #4
    I figured out a way to sort out the blanks as long as the blanks are at the end of the range. The code that is posted below works for me. It sorts data from blanks and inserts them into sheet 2.

    Sub SortInsert()

    'Sorts out blanks from the bottom of the given range and inserts them into sheet 2

    Range("A1:A20").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$20").AutoFilter Field:=1, Criteria1:="<>"
    Selection.Copy
    Sheets("Sheet2").Select
    Selection.Insert Shift:=xlDown
    Sheets("Sheet1").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •