Consulting

Results 1 to 5 of 5

Thread: Sleeper: Moving data

  1. #1
    VBAX Newbie
    Joined
    Jul 2004
    Posts
    3
    Location

    Sleeper: 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

  2. #2
    VBAX Contributor Richie(UK)'s Avatar
    Joined
    May 2004
    Location
    UK
    Posts
    188
    Location
    Hi b,

    Welcome to the board

    One approach would be to use a small function to establish the next row to use each time through the loop. Something like this:

    Sub Test()
        Dim lRow As Long
        lRow = NextRow("Sheet1")
        Worksheets("Sheet1").Cells(lRow, "A").Value = "Test"
    End Sub
    
    Function NextRow(strSheetName As String) As Long
        Dim lLast As Long
        Const lStart As Long = 8  'the default start row
        Const lOffset As Long = 6 'the number of rows to offset by
    lLast = Worksheets(strSheetName).Cells(Rows.Count, "A").End(xlUp).Row
        If lLast < 8 Then
        NextRow = lStart
        Else
        NextRow = lLast + lOffset
        End If
    End Function
    Try it in a blank workbook and you'll soon get the idea of how it works.

    A couple of bits of advice, if you are interested.

    1. Get into the habit of declaring row variables as Longs rather than Integers. The latter may not always be sufficient and its one less thing to worry about when error-checking if you always use Longs.

    2. Avoid using Select and Activate in your code where possible - they are rarely necessary and just make your code slower.

    HTH
    Last edited by Aussiebear; 04-29-2023 at 08:21 PM. Reason: Adjusted the code tags

  3. #3
    VBAX Newbie
    Joined
    Jul 2004
    Posts
    3
    Location
    I tried the code like this in the place of my commented code:

    Dim NextRow As Long
    Dim lLast As Long
    Const lStart As Long = 8 'the default start row
    Const lOffset As Long = 6 'the number of rows to offset by
    lLast = Worksheets(strSheetName).Cells(Rows.Count, "A").End(xlUp).ROW
    If lLast < 8 Then
        NextRow = lStart
    Else
        NextRow = lLast + lOffset
    End If
    **I get a run time error '9' ; Script out of Range:

    On this line:
    lLast = Worksheets(strSheetName).Cells(Rows.Count, "A").End
    Last edited by Aussiebear; 04-29-2023 at 08:22 PM. Reason: Added code tags

  4. #4
    VBAX Contributor Richie(UK)'s Avatar
    Joined
    May 2004
    Location
    UK
    Posts
    188
    Location
    Hi,

    Did you, as I suggested, try the code (as posted) in a blank workbook? You will notice that the Test sub passes an argument to the NextRow function - the strSheetName argument. If you have not defined this variable in your amended code you will need to do so before the code segment will work.

    In general, it is a lot easier to debug your code if it is written in a modular style. In this particular case it would be simpler if you made use of the UDF as written.

  5. #5
    VBAX Newbie
    Joined
    Jul 2004
    Posts
    3
    Location
    I tried it in a black workbook and it didn't do anything. I defined strSheetName and still did not get it to do anything.

    I have to put this code inside a FOR loop, so I can not end a sub or start a new function in the middle of it. I appreciate you help, thank you.

    maybe i am not understanding correctly?

    The code that I posted is just a portion of my code to do one thing. I got it to jump to the correct sheets, and now just need to put it in the right row instead of the bottom of the sheet.

Posting Permissions

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