Consulting

Results 1 to 5 of 5

Thread: Moving data

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Newbie
    Joined
    Jul 2004
    Posts
    3
    Location

    Moving data

    Hi,
    I have this code that takes data out of a worksheet and puts in at the bottom of its respective worksheet.

    I don't want it to go to the bottom and want to put the first one in row A8 and offset the rest by 6 rows down. so the second row will go to A14, 3rd A20,....


    Dim rngDataToProcess As Range                     'Loop to move new data to its worksheet
      Dim intNextRowToProcess As Integer
      Dim strStartCell As String
      Dim strEndCell As String
      Dim strRowToProcess As String
      Dim strSrcSheet As String
      Dim strDstSheet As String
      Dim intAddNewRowPosition
      Dim strDataStartThisRow As Integer
      strStartCell = "B5"
      strEndCell = "B527"
      strSrcSheet = "MODIFIED"
      strDataStartThisRow = 3
    Set rngDataToProcess = Range(strStartCell & ":" & strEndCell)
      For intNextRowToProcess = 0 To rngDataToProcess.Rows.Count - 1
        Worksheets(strSrcSheet).Activate          ' Start at the Sheet that contains the source data
        strDstSheet = rngDataToProcess.Cells(1, 1)    ' Read the row to determine the destination sheet
        strRowToProcess = CStr(strDataStartThisRow + intNextRowToProcess) & ":" & CStr(strDataStartThisRow + intNextRowToProcess) 
        ' Select Row in the worksheet to process
        Rows(strRowToProcess).Select
        Selection.Cut         ' Cut the row so that it can be moved to the appropriate Sheet
        Worksheets(strDstSheet).Activate              ' Move to destination sheet
        '      ActiveCell.SpecialCells(xlCellTypeLastCell).Select        ' Find the row to add the new data
        '       intAddNewRowPosition = ActiveCell.ROW + 1
        '      Rows(intAddNewRowPosition & ":" & intAddNewRowPosition).Select    ' Select the row where the data will be moved
        ActiveSheet.Paste                 ' Put the data in the new row
    Next intNextRowToProcess              'END of move data
    **the rows with comments is where the code has to go and is the code that puts it on the bottom of each sheet

    i hope this makes sense, please let me know.
    Thanks
    Last edited by Aussiebear; 04-29-2023 at 08:20 PM. Reason: Adjusted the code tags

Posting Permissions

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