Macro to Search, Copy/Paste onto New Sheet

**JADownie** · 02-13-2008, 01:29 PM

Hello,

I have a data file that ID #'s and their associated reponses to various questions asked. In addition to their coded responses to multiple choice questions, they are also given the option to provide additional comments, however that is not required. Therefore some people write comments, and otehrs do not. I am trying to write a macro that would locate those respondents who wrote a comment (in column M) and then paste their comment and the related project # (in column B) and paste that onto the sheet for which the comments are for (identified in column F).

I'm not sure if this is even something that can be done, or if this is too complex, so any feedback and assistance would be greatly appreciated! Thanks so much in anvance! Below are some of the specifics that I tried to write out.

Select Sheet Raw Data

Search for the first active cell in column M

Within that row containing the active cell in column M:
Copy and Paste the following cells onto a new sheet in the first blank row on Sheet based on value in column F (detailed below)

If value in column F equals "Design Services" then copied selections (listed below) are copied into sheet name "Design Services"

If value in column F equals "Panel" then copied selections (listed below) are copied into sheet name "Panel"

If value in column F equals "Telephone" then copied selections (listed below) are copied into sheet name "Telephone"

If value in column F equals "Interactive" then copied selections (listed below) are copied into sheet name "Interactive"

If value in column F equals "Mail" then copied selections (listed below) are copied into sheet name "Mail"

If value in column F equals "MCP" then copied selections (listed below) are copied into sheet name "MCP"

If value in column F equals "Field Services" then copied selections (listed below) are copied into sheet name "Field Services"

If value in column F equals "CATI" then copied selections (listed below) are copied into sheet name "CATI"

If value in column F equals "Coding" then copied selections (listed below) are copied into sheet name "Coding"

If value in column F equals "DP" then copied selections (listed below) are copied into sheet name "DP"

If value in column F equals "AA&D" then copied selections (listed below) are copied into sheet name "AA&D"

If value in column F equals "Research Excellence" then copied selections (listed below) are copied into sheet name "Research Excellence"

-Value in column B (from sheet Raw Data) gets pasted into column A on the first blank row (paste into sheet specified above)
-Value in column F (from sheet Raw Data) gets pasted into column C on the first blank row (paste into sheet specified above)
-Value in column M (from sheet Raw Data) gets pasted into column B on the first blank row (paste into sheet specified above)

I would then like the macro to continue cycling through column M until it gets to the last active cell.

**hexaflexagon** · 02-13-2008, 03:28 PM

Originally Posted by JADownie

Hello,

I have a data file that ID #'s and their associated reponses to various questions asked. In addition to their coded responses to multiple choice questions, they are also given the option to provide additional comments, however that is not required. Therefore some people write comments, and otehrs do not. I am trying to write a macro that would locate those respondents who wrote a comment (in column M) and then paste their comment and the related project # (in column B) and paste that onto the sheet for which the comments are for (identified in column F).

I'm not sure if this is even something that can be done, or if this is too complex, so any feedback and assistance would be greatly appreciated! Thanks so much in anvance! Below are some of the specifics that I tried to write out.

Select Sheet Raw Data

Search for the first active cell in column M

Within that row containing the active cell in column M:
Copy and Paste the following cells onto a new sheet in the first blank row on Sheet based on value in column F (detailed below)

If value in column F equals "Design Services" then copied selections (listed below) are copied into sheet name "Design Services"

...snipped

Hi,

Try the following VBA macro. It assumes your data sheet is named "Data"

Sub CopyComments()
Dim x As Long, stComment As String, stSheetName As String, stProjectNo As String
Worksheets("Data").Activate
    For x = 1 To Range(Range("M2"), Range("M65536").End(xlUp)).Rows.Count
        If Range("M1").Offset(x, 0) <> "" Then
            stComment = Range("M1").Offset(x, 0)
            stSheetName = Range("M1").Offset(x, -7)
            stProjectNo = Range("M1").Offset(x, -11)
            Worksheets(stSheetName).Activate
            Worksheets(stSheetName).Range("A65536").End(xlUp).Offset(1, 0) = stProjectNo
            Worksheets(stSheetName).Range("B65536").End(xlUp).Offset(1, 0) = stComment
            Worksheets("Data").Activate
        End If
    Next x
End Sub

**VBA Noob** · 02-13-2008, 03:57 PM

JADownie,

Please read forum rules and message to cross posters below

VBA Noob

Thread: Macro to Search, Copy/Paste onto New Sheet

Thread Tools

Display

Macro to Search, Copy/Paste onto New Sheet

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions