Moving data

[brocfpl]

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 ;)

[Richie(UK)]

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

[brocfpl]

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

[Richie(UK)]

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.

[brocfpl]

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.