Copy data into new rows (row 2, 3, 4, et cetera)

LSKDJF 2 Posts Wednesday January 31, 2018Registration date January 31, 2018 Last seen - Jan 31, 2018 at 12:13 AM - Latest reply: ac3mark 8804 Posts Monday June 3, 2013Registration dateModeratorStatus February 19, 2018 Last seen
- Feb 2, 2018 at 10:49 PM
To Whom It May Concern:

I'm trying to get data that's already made into a different part of the same sheet. I want to make sure it's recorded in order like a log. So it'll put together something like this:

East Coast Store Date Time Product X
West Coast Store Date Time Product Y

Again the data is okay just moving it is the problem. I can't figure out how to ensure the new data is put into the proper row by having excel recognize the row is empty or full and how many it should move down until it finds an empty row to put the data. That way row 1 is used first, then 2, then 3, et cetera. If I come back into the worksheet and the first 5 rows are taken it should go straight into row 6. How can I best achieve this?

Sincerely,

Greg
See more 

6 replies

Reply to this topic
ac3mark 8804 Posts Monday June 3, 2013Registration dateModeratorStatus February 19, 2018 Last seen - Jan 31, 2018 at 04:44 PM
LSKDJF 2 Posts Wednesday January 31, 2018Registration date January 31, 2018 Last seen - Jan 31, 2018 at 10:25 PM
ac3mark,

would i use the following portion to actually do the placement of text?

Function LoopForMove(FromWhatSheet, ToWhatSheet)
Dim LastRow, Cnt
Dim CellValue As String
Dim CellLoc
Dim nret
If WhatCol = "" Then
WhatCol = "A"
End If
If Qualif = "" Then
Qualif = "X"
End If
ThisWorkbook.Worksheets(FromWhatSheet).Select
LastRow = FindLastRow(FromWhatSheet)
For Cnt = LastRow To 1 Step -1
CellLoc = WhatCol & Cnt
CellValue = ThisWorkbook.Worksheets(FromWhatSheet).Range(CellLoc).Value
If CellValue = Qualif Then
nret = Moveit(FromWhatSheet, CellLoc, ToWhatSheet, CutVal)
End If

If that's the case I would simply alter the place from moving it to a separate sheet, like the example uses, to just the upper portion of the same sheet. Put all of that code into one simple button and that would copy whatever text I've designated and place it in order automatically like I wanted. Did I get that right?

Thanks for the help!

Greg
ac3mark 8804 Posts Monday June 3, 2013Registration dateModeratorStatus February 19, 2018 Last seen - Feb 2, 2018 at 06:58 PM
Yes, just reference the same sheet!

You will still need to pass the variable of "ToWhatSheet" tho. What would you do to eliminate that need?
Well, either remove the reference entirely or put it from Worksheet A to Worksheet A essentially (if it's mandatory). I'd use this assuming that sheet1 is the name of my worksheet and I'd want to have the information end up in column B:

Function LoopForMove(Sheet1, ToSheet1)
Dim LastRow, Cnt
Dim CellValue As String
Dim CellLoc
Dim nret
If WhatCol = "" Then
WhatCol = "B"
End If
If Qualif = "" Then
Qualif = "X"
End If
ThisWorkbook.Worksheets(Sheet1).Select
LastRow = FindLastRow(Sheet1)
For Cnt = LastRow To 1 Step -1
CellLoc = WhatCol & Cnt
CellValue = ThisWorkbook.Worksheets(Sheet1).Range(CellLoc).Value
If CellValue = Qualif Then
nret = Moveit(Sheet1, CellLoc, Sheet1, CutVal)
End If

I'd want to move information from F2 through F5 into B2 through B5 on the same worksheet then clear F2 through F5. Having to fix the cell values a bit more would what I have here be effective?
Ideally I'd also like to incorporate a timestamp into the data once moved as well. That way I know exactly when i moved it and the time/date won't update after the information is moved. I'm having trouble with this as well. Is there anyway to combine those two things into one macro?
ac3mark 8804 Posts Monday June 3, 2013Registration dateModeratorStatus February 19, 2018 Last seen - Feb 2, 2018 at 10:49 PM
Yes, but you don't want to. You want 1 macro to call three separate procedures!

That way you can isolate the procedures and where the trouble may fail!

BTW, just drop the second passing of the variable, as in:
LoopForMove(Sheet1)
Respond to ac3mark