Previous Topic Next Topic

Copy to first open cell in rowB/sheet 2

Solved/Closed
Stormdronk - Apr 5, 2011 at 08:04 AM
 Stormdronk - Apr 20, 2011 at 06:04 AM
Hello,

Thanks for trying to help!

I have a sheet as shown below. We do sales every day and would like the values/ estimates and sales that have a status of 4 to be copyd to a total sheet (sheet 2). The content must be copyd as soon as the status becomes 4 and automaticly be moved to the next open cell,s in sheet2.(ranged cells)

Hope this is clear

Sheet 1

Estimate Sale Status
R 10,000.00 R 10,000.00 4
R 5,000.00 R 5,000.00 2
R 3,000.00 1
R 5,000.00 R 5,000.00 4

Sheet 2
Estimate Sale Status
R 10,000.00 R 10,000.00 4
R 5,000.00 R 5,000.00 4

Are there a formula that can do this, dont know macros jet?

Tanks again for the help!


Related:

3 responses

Answer 1 / 3
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Apr 8, 2011 at 09:33 AM
Hi Stormdronk,

Macro's can NOT be undone by hitting the blue arrow. So SAVE your file before running or in this case implementing codes, so you are able to restore your file.

See if this code works for you: 

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B2:B10")) Is Nothing Then Exit Sub
Set MR = Range("B2:B10")
    For Each cell In MR
If cell.Value = 4 And cell.Offset(0, 1).Value <> "C" Then
cell.EntireRow.Copy
Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
cell.Offset(0, 1).Value = "C"
cell.Offset(0, 1).Font.ColorIndex = 2
        End If
            Next
    Application.CutCopyMode = False
End Sub

1. Implementing the code:
Right-click on the sheets tab and choose the bottom option called: view code.
Copy/paste the code in the big white field of the new window that should appear.

2. Adjusting the code:
I have used the range ("B2:B10") twice in the code, codeline 3 and 4. This represents the range in which you put the status number. Change this range to match your sheet.

The column next to the status column is used to see whether the row has been copied already or not. This is done by placing a white (so you can't see it) C in it. If you use this column for something else you want to move 1 or more columns to the right so it doesn't affect your data. This done on the 5th line of code: 
Offset(0,1)
Last this to change is the name of your destination sheet. This is done on the 7th line of code. The name I used is: Sheet2.

3. Testing code.
Now for the fun part. When altering you sheet outside the target range (B2:B10), nothing should happen. Otherwise the row with a status 4, which have not been copied before, will be copied to the destination sheet. Which should put a smile on your face :) .

Please do let me know how this is working out for you.

Best regards,
Trowa
0
Answer 2 / 3
Stormdronk
Apr 12, 2011 at 04:30 AM
Hi Trowa,

Thanks a lot this works grate!!!!

Just a nother question, is it 1st posible to copy a range and not the entire row? 2nd can the copy be undone if I change the stutus let say back to 3, and last question can the status range be in more than one sheet. My result is in sheet1 and then my status is in multible sheets, let say (B2:B10) in sheet2,3,4 and 5. If their status are 4 then copy them to my result sheet1.

I know this is probably a lot to ask, I appreciate your help!!

Thanks, Henk
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Apr 12, 2011 at 10:43 AM
Hi Henk,

Glad to see you appreciate the answer.

Let's take a look at your questions:

1 - Yes you can. Replace this line:
cell.EntireRow.Copy
to something like this:
Range(cell.Offset(0, -1), cell.Offset(0, 1)).Copy
This will copy column A to C of the relative row.

2 - This is a tricky one.

3 - To do this you will have to copy the code to the other sheets as well.
Just right-click on the other sheets' tab to implement the code.

I will have to look into question number 2 some more. Unfortunately I'm out of time for today.
So you will have to wait a little more. Do comment on the first and third answers.

Best regards,
Trowa
0
Answer 3 / 3
Stormdronk
Apr 12, 2011 at 01:28 PM
Hi Trowa

Ok I am impressed, this works perfectly.

Just on the ranges, I opend a new workbook in excel and tested your code and everything works fine, now I need to understand how to change the ranges so it soots my workbook.

Sheet1 is my results sheet, in this sheet a copy all the data from the other sheets.

In sheet 1 I use cells D12:R12 and down, I use the top rows to do culculations in.

in sheets 2,3,4,5.... I use E12:S12 and down, these will copy to sheet 1 as per your coding.

The status cells are In T12 and down in sheets 2,3,4,5....

I Am sorry to ask you this, but I dont understand how the coding of

"Range(cell.Offset(0, -1), cell.Offset(0, 1)).Copy"

gives you the result of A:C .

Thanks Bud you are helping a lot!!!!!!!

Regards, Henk
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Apr 14, 2011 at 10:19 AM
Hi Henk,

In the example I used column B as status column.
Looking at the code line: 
Range(cell.Offset(0, -1), cell.Offset(0, 1)).Copy
Cell refers to the cells in column B.
Offset(0,-1) means: shift 0 rows down, shift 1column to the left.
Offset(0,-1) means: shift 0 rows down, shift 1column to the right.
So 1 column to the left and 1 column to the right of column B means column A:C.

In your case you are using column T as status column and you want to copy column E:T.
The code line will then look like this: 
Range(cell.Offset(0, -15),cell).Copy
Now cell refers to column T and cell.Offset(0,-15) refers to column E (15 columns to the left of column T.)

You didn't asked, so I guess it's clear. But just to be complete, the code line: 
Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
needs to be altered into: 
Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
Because Sheet1 is result sheet (not sheet2) and
you want to paste starting at column D (not column A).


Now the answer for question 2: undo the copy/paste.

I assumed that the cell to the left of the status column (T) is an unique value.
The assumption comes forward on line 4 and 14.
Start by looking at code line 14: 
x = cell.Offset(0, -1).Value
cell refers to the status column (T), offset(0,-1) means: look 1 column to the left.
The value of that cell is used to search the result sheet (sheet1). If this is not an unique value then change the offset value so that it targets a column with unique values.
Code line 4: 
Set MR2 = Sheets("blad2").Range("R12:R100")
is the column used to find the previously mentioned search value.
Change this range when you change the offset value.

Here is the code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("T12:T100")) Is Nothing Then Exit Sub
Set MR = Range("T12:T100")
Set MR2 = Sheets("blad2").Range("R12:R100")

    For Each cell In MR

If cell.Value = 4 And cell.Offset(0, 1).Value <> "C" Then
Range(cell.Offset(0, -15), cell).Copy
Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
cell.Offset(0, 1).Value = "C"
cell.Offset(0, 1).Font.ColorIndex = 2
        End If

If cell.Value <> 4 And cell.Offset(0, 1).Value = "C" Then
cell.Offset(0, 1).ClearContents
x = cell.Offset(0, -1).Value
        End If
            Next
            
            
    For Each cell In MR2
    
If cell.Value = x Then
Sheets("blad2").Range(cell.Offset(0, -14), cell.Offset(0, 1)).ClearContents
        End If
            Next
            
Application.CutCopyMode = False
    
End Sub

Please feel free to ask away.
I just hope my explanations are clear.

Best regards,
Trowa
0
Stormdronk
Apr 20, 2011 at 06:04 AM
Hi,

Trowa, sorry I am only coming back to you now, hectic week!!

The code works grate thanks!!

I realy appreciate your help.

Regards,

Henk
0