Help with Excel/VBA

[DavidAndrew]

Hi all,

Looking for some help with writing of VBA code for an excel sheets I am working on.

I have multiple worksheets on each of which have a "Current Status" column which I have a filter applied too, it has a few different options, complete, not started, in progress etc. Dependant on the status of this filter - I would like the row of this cell to be moved too a separate worksheet - called Status. I do not want the row to be removed - but copied across. I want this to work when I continue to update the sheet in the future also.

Additionally I would like to be able to only select certain columns from the row to appear on the "Status" worksheet - i.e not all the columns be moved across.

I am a total VBA novice and any advice would be greatly appreciated!

Many thanks,
David

 

[DavidAndrew]

Hi Yongle,

Thanks again for taking the time to reply.

I am learning more here and I have carried out the following tasks.

Firstly I saved the copy of the excel document as macro enabled.

Secondly I copied your code across and what it did was copy across across the first row of the sheet I want to copy from and moved it to my destination sheet. Was this what was expected to happen? I wasn't sure if it should be doing more after this code. the "Set Status" line of the code has confused me a little.

Additionally I have had to make some changes. The column values required are now the folloiwng:

A to C, G, AS to CO. With G still being the "Current Status"

I THINK I understand that i would need to change the Resize Value to the following:

For A_C change to 3 and for AS to CO change to 49.

Is the above right in thinking? I did indeed recieve the "Macro is running" pop up message.

 

[Yongle]

You are making the mistake almost everyone coming from Excel to VBA does ...
Lesson 1
VBA is NOT excel (insert a column and Excel re-references every formula)
VBA is much more rigid
- Range("C:C") is ALWAYS column C even if you insert a new column before column B
- Excel knows that the old C is now D
- VBA does not and the has to be manually amended to Range("D:D")
Make your mind up as best as you can regarding sheet structure and automation process etc BEFORE starting to write any code
Making amendments is time consuming and leads to errors

I am trying to make this easy for you , but you are unwittingly making life difficult by moving the goalposts repeatedly
The original variable names were chosen to help you see exactly what is happening in the code (especially when you look at it in 3 months time when you amendments required)
More generic variable names have been substituted given that you are likely to want to change them again before you arrive at your final solution
Note how OFFSET is determined
- the zero before the comma (= row offset) can be omitted like this ..

            rng2.Copy destn.Offset(, rng1.Columns.Count)
            rng3.Copy destn.Offset(, rng1.Columns.Count + rng2.Columns.Count)

STEP 2
Previous code re-written to reflect your changes PLUS condition added
Test as before

Sub MacroTest2()
    Dim sh As Worksheet, Cel As Range, Status As Range, rng1 As Range, rng2 As Range, rng3 As Range, destn As Range
    Set sh = ActiveSheet
    Set Cel = ActiveCell
    Set Status = sh.Cells(Cel.Row, 7)
    Set rng1 = sh.Cells(Cel.Row, "A").Resize(, 3)
    Set rng2 = sh.Cells(Cel.Row, "G")
    Set rng3 = sh.Cells(Cel.Row, "AS").Resize(, 49)
    Set destn = Sheets("Status").Cells(Rows.Count, 1).End(xlUp).Offset(1)
    Select Case LCase(Status)
        Case "completed", "work in progress", "received", "ordered"
            rng1.Copy destn
            rng2.Copy destn.Offset(0, rng1.Columns.Count)
            rng3.Copy destn.Offset(0, rng1.Columns.Count + rng2.Columns.Count)
    End Select
End Sub

the "Set Status" line of the code has confused me a little.

The line is setting range variable Status
It was not used in MacroTest1
It is used in MacroTest2 in select case test
Select Case is being used instead of ....

IF Status = "Completed" Or Status = "Work in progress" Or Status = "Received" Or Status = "Ordered" Then

(Status wrapped in LCase and tested against lower case values avoids any case sensitive issues)

STEP 3 ??
1. Is the above doing what it should be doing ?
2 Please tell me what the trigger should be for the row to be auto-copied
- amending the value in which column should trigger the copy ?

 

[Yongle]

"Set Status" line of the code has confused me a little

further explanation
- these 3 lines of code are the same

    Set Status = sh.Cells(Cel.Row, 7)
    Set Status = sh.Cells(Cel.Row, "G")
    Set Status = sh.Range("G" & Cel.Row)

 

[DavidAndrew]

You are making the mistake almost everyone coming from Excel to VBA does ...
Lesson 1
VBA is NOT excel (insert a column and Excel re-references every formula)
VBA is much more rigid
- Range("C:C") is ALWAYS column C even if you insert a new column before column B
- Excel knows that the old C is now D
- VBA does not and the has to be manually amended to Range("D:D")
Make your mind up as best as you can regarding sheet structure and automation process etc BEFORE starting to write any code
Making amendments is time consuming and leads to errors

I am trying to make this easy for you , but you are unwittingly making life difficult by moving the goalposts repeatedly
The original variable names were chosen to help you see exactly what is happening in the code (especially when you look at it in 3 months time when you amendments required)
More generic variable names have been substituted given that you are likely to want to change them again before you arrive at your final solution
Note how OFFSET is determined
- the zero before the comma (= row offset) can be omitted like this ..

            rng2.Copy destn.Offset(, rng1.Columns.Count)
            rng3.Copy destn.Offset(, rng1.Columns.Count + rng2.Columns.Count)

STEP 2
Previous code re-written to reflect your changes PLUS condition added
Test as before

Sub MacroTest2()
    Dim sh As Worksheet, Cel As Range, Status As Range, rng1 As Range, rng2 As Range, rng3 As Range, destn As Range
    Set sh = ActiveSheet
    Set Cel = ActiveCell
    Set Status = sh.Cells(Cel.Row, 7)
    Set rng1 = sh.Cells(Cel.Row, "A").Resize(, 3)
    Set rng2 = sh.Cells(Cel.Row, "G")
    Set rng3 = sh.Cells(Cel.Row, "AS").Resize(, 49)
    Set destn = Sheets("Status").Cells(Rows.Count, 1).End(xlUp).Offset(1)
    Select Case LCase(Status)
        Case "completed", "work in progress", "received", "ordered"
            rng1.Copy destn
            rng2.Copy destn.Offset(0, rng1.Columns.Count)
            rng3.Copy destn.Offset(0, rng1.Columns.Count + rng2.Columns.Count)
    End Select
End Sub

The line is setting range variable Status
It was not used in MacroTest1
It is used in MacroTest2 in select case test
Select Case is being used instead of ....

IF Status = "Completed" Or Status = "Work in progress" Or Status = "Received" Or Status = "Ordered" Then

(Status wrapped in LCase and tested against lower case values avoids any case sensitive issues)

STEP 3 ??
1. Is the above doing what it should be doing ?
2 Please tell me what the trigger should be for the row to be auto-copied
- amending the value in which column should trigger the copy ?

 

[DavidAndrew]

Thank you again for taking the time to reply,

My apologies for changing the goal posts surrounding the columns - and thank you for trying to accommodate once again and future planning for me!

Thanks for explaining the Status conundrum, but I have to admit it hasn't answered it fully. I am confused as to which "Status" is referring to the column and which "Status" is referring to the worksheet. Did you amend the code to change the COLUMN to "Current Status"? I do not see any mention of that with the code. Sorry to exacerbate you with this, just I want to understand it as it goes. For example there are 4 mentions in the code you have sent that mention Status - which refer to which is my question? I have changed Column G to Current Status and I fear this is why it may not be working.

To answer your questions - I have tested the newly code and all that happens I am asked if I would like to save the Macro and then I press Run. All that has happened is the top row is now populated with a blank row of Cells on my destination Sheet " Status".

The trigger should be that if the following worksheets have "work in progress", "completed", "received or "ordered" data in column G that the row on which that data has changed should then automatically move across to the "Status" worksheet. Here are the worksheets required to have data sent to "Status":

Sheet 2
Sheet 4-25
Sheet 31 -32

Thanks very much I look forward to hearing from you!

 

[Yongle]

What does this mean ?
Sheet 2
Sheet 4-25
Sheet 31 -32

Are these the actual names of the sheets (including space) to be included?
"Sheet 2"
"Sheet 4" , "Sheet 5" , "Sheet 6" ....."Sheet 25"
"Sheet 31", "Sheet 32"

 

[Yongle]

As stated previously what you are requesting is complicated for a VBA novice
For the moment ...

1. Keep your questions in a list but do not post them
2. I will answer any questions relating to the FINALISED code AFTER you tell me that the code is doing everything that you want
3. Simply place any code I give where I tell you and test it
4. Please do not try to amend it yourself EXCEPT where I ask you to
- there may be consequences elsewhere in the code that are not immediately obvious
5. After testing tell me ..
- what the code is NOT doing that it should be
- and what it is doing that it should not be

Instructions
1. Delete ALL previous code
2. To apply to multiple sheets, the code must be placed in ThisWorkbook code window (see below)
3. ONLY AMEND this line to reflect the actual names of the sheets to be EXCLUDED

Case "Status", "Sheet 1", "Sheet 3"   'etc

Testing
Try amending values in sheets that should be included and those that should be excluded

This code must be placed in ThisWorkbook code window (see below)

Option Explicit

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
    Set Target = Cells(Target.Row, "A")
    If Target.Row = 1 Then Exit Sub
    If Not StatusListed(Range("G" & Target.Row)) Then Exit Sub
    If IncludeSheet(sh) Then Call CopyRow(Target, Sheets("Status"))
End Sub

Private Function IncludeSheet(ByVal sh As Object) As Boolean
    IncludeSheet = False
    Select Case sh.Name
        Case "Status", "Sheet 1", "Sheet 3"   'list sheets to be EXCLUDED HERE
        Case Else:  IncludeSheet = True
    End Select
End Function

Private Function StatusListed(ByVal CurrentStatus As String) As Boolean
    Select Case LCase(CurrentStatus)
        Case "completed", "work in progress", "received", "ordered"
            StatusListed = True
    End Select
End Function

Private Sub CopyRow(Target As Range, ws As Worksheet)
    Dim sRow As Long
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    With ws
        sRow = .Cells(Rows.Count, "D").End(xlUp).Row + 1            'next row in sheet Status
        Target.Resize(, 3).Copy .Cells(sRow, "A")                   'copy A:C
        Target.Offset(, 6).Copy .Cells(sRow, "D")                   'copy G
        Target.Offset(, 44).Resize(, 49).Copy .Cells(sRow, "E")     'copy AS:CO
    End With
    Application.EnableEvents = True
End Sub

Placing in ThisWorkbook code window

 

[DavidAndrew]

What does this mean ?
Sheet 2
Sheet 4-25
Sheet 31 -32

Are these the actual names of the sheets (including space) to be included?
"Sheet 2"
"Sheet 4" , "Sheet 5" , "Sheet 6" ....."Sheet 25"
"Sheet 31", "Sheet 32"

Hi Yongle,

These are not the ACTUAL names of the sheets. Please see attached picture

So the column G on the mentioned sheets needs to be copied to Sheet41 (Status) - when the row contains anything other than a blank.

Kind regards,

David

 

[Yongle]

To avoid a very challenging ride be VERY precise with EVERYTHING you tell us
- VBA is unforgiving but is well behaved and does EXACTLY what it is told ( "rubish in = rubish out" )

There are 3 ways to refer to sheets
- for the purpose of this example sheet named "Status" is presumed to be the TENTH sheet in your workbook

The sheet TABname - which is in brackets in your picture
Sheets("Status").Range("A1")

The sheet CODEname - which is the name before the brackets in your picture
Sheet41.Range("A1")

The POSITION of the sheet in the workbook (or to give it the proper teminology the sheet's INDEX number)
Sheets(10)

With that new knowledge, please correct what you told me before
Sheet 1
Sheet 4-25
Sheet 31 -32

So piecing together (which should not be necessary) based on what you have told me
Is Sheet 4 ?
- the 4th sheet in the workbook probably not
- sheet named "Sheet 4" it is not becasuse that sheet name is not in the picture!
- sheet with codename Sheet4 I think so

Please let me know
Thanks

 

[DavidAndrew]

Hi again Yongle,

Thanks once again for taking the time to respond. I understand the different terminology better now and will explain.

What I was referring to was indeed the CODEnames.

So I need to copy data from CODEnames:

Sheet 1
Sheet 4-25
Sheet 31 -32

TO:

Sheet41

I hope this explains it better?

Many thanks again for your time,

David

So piecing together (which should not be necessary) based on what you have told me
Is Sheet 4 ?
- the 4th sheet in the workbook probably not
- sheet named "Sheet 4" it is not becasuse that sheet name is not in the picture!
- sheet with codename Sheet4 I think so