Help with Excel/VBA

DavidAndrew

New Member
Joined
May 21, 2020
Messages
19
Office Version
  1. 2013
Platform
  1. Windows
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Welcome to the forum

I have multiple worksheets
does this apply to EVERY sheet in the workbook EXEPT sheet "Status" ?

I would like the row of this cell to be moved copied to a separate worksheet - called Status
what should trigger the copying ?
- which column being edited ?

Dependant on the status of this filter
which status value results in the row being copied ?

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.
which columns ?

In principle you require you need to use Workbook_SheetChange which sits in ThisWorbook code window (NOT a module like Module1)

VBA Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
' above details required to determine code
End Sub
 
Upvote 0
Welcome to the forum


does this apply to EVERY sheet in the workbook EXEPT sheet "Status" ?

No actually it applies to lots of them but not all of them!


what should trigger the copying ?
- which column being edited ?

Anything in the Current Status column (column "G") which is not blank or not being progressed with

which status value results in the row being copied ?

So this is a bit trickier! I need it to move only the status values that say "Completed" Work In Progress" Received" Ordered". However not to move the blank status OR the "Not being progressed" status'. Is this possible or would it be better to make separate tabs for each?

which columns ?

So I need to populate all the worksheet "Status" columns - however NOT ALL off the status columns, this is where I run into problems! So in the worksheets I would like copied across - I would like columns A-G and also L-Q. All of these columns are present in the status.

Thank you very much for taking the time to reply and for the welcome!

In principle you require you need to use Workbook_SheetChange which sits in ThisWorbook code window (NOT a module like Module1)

VBA Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
' above details required to determine code
End Sub
 
Upvote 0
1. Test on a COPY of your workbook
2. The code is triggered when the value in column G is changed by the user
3. It should be close to what you want
4. Test it and come back with details of what needs to operate differently

5. In the code below ...
(a) amend status values if not correct
- put everything in LOWER case in the code (regardless of what is in the worksheet)
- the code tests for the lower case value of status text
VBA Code:
Case "completed", "work in progress", "received", "ordered"

(b) In this line include all EXCUDED sheets - names in quotes and separated by commas
VBA Code:
Case "Status", "New Potatoes", "Fresh Dates"


Place code in ThisWorkbook code window (NOT a module like Module1, NOT in sheet code window - neither will work)
VBA Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Const Trigger = "G"
    Dim Cel As Range, A_G As Range, L_Q As Range, Status As Range, triggerRng As Range, pasteRng As Range, r As Long
    Set Cel = Target.Cells(1, 1)
    Set Status = Sh.Cells(Cel.Row, 7)
    Set A_G = Sh.Cells(Cel.Row, "A").Resize(, 7)
    Set L_Q = Sh.Cells(Cel.Row, "L").Resize(, 6)
    Set triggerRng = Sh.Cells(2, Trigger).Resize(Rows.Count - 1)
    Set pasteRng = Sheets("Status").Cells(Rows.Count, 1).End(xlUp).Offset(1)

    Select Case Sh.Name
        Case "Status", "New Potatoes", "Fresh Dates"
            'the above sheets are ignored

        Case Else
            If Not Intersect(Cel, triggerRng) Is Nothing Then
                Select Case LCase(Status)
                    Case "completed", "work in progress", "received", "ordered"
                        A_G.Copy pasteRng
                        L_Q.Copy pasteRng.Offset(, 7)
                End Select
            End If
    End Select
End Sub

CopyRows.jpg
 
Upvote 0
Thanks again for taking the time to reply. I am working through the sent code and trying to get it to work on my excel sheet.

As a beginner I have some questions.

How is this code implemented?

When I try to save "ThisWorkbook" I get the below message. From what I have read online - do I need to enable a macro button for this to work? I have wrote the code om a VBA project and I nothing is happening?

I need to code too copy from certain sheets, say for example in yours Sheets 3, 4 and 5 I want to the take information from and put it into Sheet 1 (Status)

Additionally I realised that my descriptions had been to similar using Status as the Column G header and Status as the worksheet name. I have since changed the Column G name too Current Status to prevent mix ups.



1590148940276.png



1. Test on a COPY of your workbook
2. The code is triggered when the value in column G is changed by the user
3. It should be close to what you want
4. Test it and come back with details of what needs to operate differently

5. In the code below ...
(a) amend status values if not correct
- put everything in LOWER case in the code (regardless of what is in the worksheet)
- the code tests for the lower case value of status text
VBA Code:
Case "completed", "work in progress", "received", "ordered"

(b) In this line include all EXCUDED sheets - names in quotes and separated by commas
VBA Code:
Case "Status", "New Potatoes", "Fresh Dates"


Place code in ThisWorkbook code window (NOT a module like Module1, NOT in sheet code window - neither will work)
VBA Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Const Trigger = "G"
    Dim Cel As Range, A_G As Range, L_Q As Range, Status As Range, triggerRng As Range, pasteRng As Range, r As Long
    Set Cel = Target.Cells(1, 1)
    Set Status = Sh.Cells(Cel.Row, 7)
    Set A_G = Sh.Cells(Cel.Row, "A").Resize(, 7)
    Set L_Q = Sh.Cells(Cel.Row, "L").Resize(, 6)
    Set triggerRng = Sh.Cells(2, Trigger).Resize(Rows.Count - 1)
    Set pasteRng = Sheets("Status").Cells(Rows.Count, 1).End(xlUp).Offset(1)

    Select Case Sh.Name
        Case "Status", "New Potatoes", "Fresh Dates"
            'the above sheets are ignored

        Case Else
            If Not Intersect(Cel, triggerRng) Is Nothing Then
                Select Case LCase(Status)
                    Case "completed", "work in progress", "received", "ordered"
                        A_G.Copy pasteRng
                        L_Q.Copy pasteRng.Offset(, 7)
                End Select
            End If
    End Select
End Sub

View attachment 14440
1. Test on a COPY of your workbook
2. The code is triggered when the value in column G is changed by the user
3. It should be close to what you want
4. Test it and come back with details of what needs to operate differently

5. In the code below ...
(a) amend status values if not correct
- put everything in LOWER case in the code (regardless of what is in the worksheet)
- the code tests for the lower case value of status text
VBA Code:
Case "completed", "work in progress", "received", "ordered"

(b) In this line include all EXCUDED sheets - names in quotes and separated by commas
VBA Code:
Case "Status", "New Potatoes", "Fresh Dates"


Place code in ThisWorkbook code window (NOT a module like Module1, NOT in sheet code window - neither will work)
VBA Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Const Trigger = "G"
    Dim Cel As Range, A_G As Range, L_Q As Range, Status As Range, triggerRng As Range, pasteRng As Range, r As Long
    Set Cel = Target.Cells(1, 1)
    Set Status = Sh.Cells(Cel.Row, 7)
    Set A_G = Sh.Cells(Cel.Row, "A").Resize(, 7)
    Set L_Q = Sh.Cells(Cel.Row, "L").Resize(, 6)
    Set triggerRng = Sh.Cells(2, Trigger).Resize(Rows.Count - 1)
    Set pasteRng = Sheets("Status").Cells(Rows.Count, 1).End(xlUp).Offset(1)

    Select Case Sh.Name
        Case "Status", "New Potatoes", "Fresh Dates"
            'the above sheets are ignored

        Case Else
            If Not Intersect(Cel, triggerRng) Is Nothing Then
                Select Case LCase(Status)
                    Case "completed", "work in progress", "received", "ordered"
                        A_G.Copy pasteRng
                        L_Q.Copy pasteRng.Offset(, 7)
                End Select
            End If
    End Select
End Sub

View attachment 14440
 
Upvote 0
OK
- this method is beyond your abilities at this stage

We will start again
- very simply
- step by step
- when I have some more time later today

In the meantime - build up some basic understanding of VBA
 
Upvote 0
I am happy to help you, but test this macro without any amendments or questions
- you can learn as you watch the macro develop
- I will explain EVERY line in the macro after you get it working

Step1
(this macro does not check the value of Current Status - that will be done in step2)

Test on a COPY of your workbook
Place code below in Module1
Select a sheet whose values should be copied to sheet "Status"
Select any row in that sheet
Run the macro

Did the message box appear ?
Is the correct range of cells copied to the correct destination ?

VBA Code:
Sub MacroTest1()
MsgBox "macro is running"
    Dim sh As Worksheet, Cel As Range, Status As Range, A_G As Range, L_Q As Range, destn As Range
    Set sh = ActiveSheet
    Set Cel = ActiveCell
    Set Status = sh.Cells(Cel.Row, 7)
    Set A_G = sh.Cells(Cel.Row, "A").Resize(, 7)
    Set L_Q = sh.Cells(Cel.Row, "L").Resize(, 6)
    Set destn = Sheets("Status").Cells(Rows.Count, 1).End(xlUp).Offset(1)
    A_G.Copy destn
    L_Q.Copy destn.Offset(, 7)
End Sub
 
Upvote 0
When I try to save "ThisWorkbook" I get the below message

1590148940276.png


Save the workbook as macro enabled

xlsm.jpg


From what I have read online - do I need to enable a macro button for this to work? I have wrote the code om a VBA project and I nothing is happening?

You may be asked to click a button to enable macros when you next open the file
 
Upvote 0
Yes I believe it is!

I have read through the website that you have sent and it has been very helpful, thank you. Looking forward to speaking to you for a step by step process.

Many thanks again,

David

OK
- this method is beyond your abilities at this stage

We will start again
- very simply
- step by step
- when I have some more time later today

In the meantime - build up some basic understanding of VBA
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top