Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2014
    Posts
    3

    Unanswered: VBA to move rows to another sheet based on criteria

    I am currently working on a pro-bono project for a local non profit and I am looking for a simple way to import their G/L data into different tabs based on a reference cell value.


    I have attached my current workbook and any help creating a VBA code would be greatly appreciated:

    On the tab "Import" the non profit is pasting lines of accounting data starting on line 3 (Line 1&2 are headers).
    I am looking for a VBA that will reference the value in column K and Copy/Paste the entire line onto the tab with the same tab title as the value in column K. (the tab titles are being created by cell B2).
    When the data gets pasted onto the tab I am looking for it to start on line 30 (its being included in pivot tables) and always paste after the last row of Data.
    This is a process they will run once a month, so I am going to assign the VBA to a macro button on the Import tab.

    Any help would be greatly appreciated!!!!!!
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2014
    Posts
    3

    Unhappy VBA Split Data and paste on Separate Tabs

    I am currently working on a pro-bono project for a local non profit and I am looking for a simple way to import their G/L data into different tabs based on a reference cell value.


    I have attached my current workbook and any help creating a VBA code would be greatly appreciated:

    On the tab "Import" the non profit is pasting lines of accounting data starting on line 3 (Line 1&2 are headers).
    I am looking for a VBA that will reference the value in column K and Copy/Paste the entire line onto the tab with the same tab title as the value in column K. (the tab titles are being created by cell B2).
    When the data gets pasted onto the tab I am looking for it to start on line 30 (its being included in pivot tables) and always paste after the last row of Data.
    This is a process they will run once a month, so I am going to assign the VBA to a macro button on the Import tab.

    Any help would be greatly appreciated!!!!!!
    Attached Files Attached Files

  3. #3
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    It would be something like this:...

    Code:
    Public Sub CopyData2Sheets()
    Dim shtImp As Worksheet
    Dim sSht As String
    Dim r As Long
    
    Set shtImp = Worksheets("IMPORT")  'get the data sheet
    shtImp.Activate
    Range("A3").Select
    
    While ActiveCell <> ""            'scan all records in IMPORT sheet
       r = ActiveCell.Row
       Range("K" & r).Select
       sSht = ActiveCell.Value                   'get the sheet target name
       Range("A" & r & ":N" & r).Select
       Selection.Copy
       
        Worksheets(sSht).Activate
        Range("B31").Select  'startg pt
        Selection.End(xlDown).Select   'find last row
        ActiveCell.Offset(1, 0).Select    'next target cell
        ActiveSheet.Paste
        Application.CutCopyMode = False
       
       shtImp.Activate
       ActiveCell.Offset(1, 0).Select    'next record to copy
    Wend
    
    Set shtImp = Nothing
    End Sub

  4. #4
    Join Date
    Apr 2014
    Posts
    3

    Thank you & Follow up

    ranman256 thank you for your help with this code. Do you know how I could ignore a line of data if I don't have a corresponding tab to copy it onto?

Tags for this Thread

Posting Permissions

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