Thread: Macro To Copy Specific Data Into A Separate Worksheet

Hi

I have the following worksheets:
- SECTION 1 - This worksheet acts like a questionnaire
- Action Plan

I would like the action plan to pick up any questions scored from 0-3 (ignoring any questions marked 'N/A'), which will then allow the user to input any actions in the 'grey' coloured cells etc. - see example

I do have further sections/questionnaire on separate worksheets and I would like macro to do the same - after finding the blank row in the action list.

I’m too good with my coding and hope someone can help or at least start me off – Many thanks!

How would the other sheets be named in your workbook? I need to know this so i can code it accordingly.

Use this code -

Option Explicit
Dim i As Long
Dim j As Long
Dim lrow As Long

Sub cons_data()

For i = 1 To Worksheets.Count
    If Worksheets(i).Name Like "Section*" Then
        lrow = Worksheets(i).Range("B" & Rows.Count).End(xlUp).Row
        For j = 3 To lrow
            If Worksheets(i).Range("C" & j).Value <> "" And Worksheets(i).Range("B" & j).Value <> "Section Total" And _
            Worksheets(i).Range("G" & j).Value <= 3 Then
                With Worksheets("Action")
                    .Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = Worksheets(i).Name
                    .Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = Worksheets(i).Range("B" & j).Value
                    .Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Value = Worksheets(i).Range("C" & j).Value
                    .Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Value = Worksheets(i).Range("G" & j).Value
                    .Range("F" & Rows.Count).End(xlUp).Offset(1, 0).Value = Worksheets(i).Range("H" & j).Value
                End With
            End If
        Next j
    End If
Next i

End Sub

What do you mean by range could be different? Are there chances of the columns changing?

Actually the macro is designed to take in how many ever questions there are. So even if it varies from sheet to sheet, it will still include them. Will provide you an updated code regarding the other changes you have suggested, shortly.

Do u want the validation as part of the code or will you be inputting it manually?

I am a little stuck with the option boxes. Will research and revert to you shortly.