Copy cells and put them into columns

bessieunl

New Member
Joined
May 19, 2015
Messages
11
in sheet1, I have a column A, where it contains several groups of data (in number format), and each group start with a cell that has this Group's name (text), I wanna to be able to copy groups (including group name and data) based on desirable group names. Each group has different number of rows. So for example, I want to have group 1 and group 3 copied and paste into column A and Column B in sheet2.

Column A
Group1
.
.
.
.
Group2
.
.
.
.
.
.
.
Group3
.
.
.
.
.
.
.
.
 
Last edited:

AliMass

New Member
Joined
Jun 4, 2015
Messages
14
I'm not sure I understand. Is it something like:

Row 1: Group 1
Row 2: 1
Row 3: 3
Row 4: 100
Row 5: Group 2
Row 6: 44
Row 7: 8
Row 8: Group 3
etc.

And you want to copy items under certain groups.

My main thought is to insert a column to the left and have that column contain the group headings for every row. Thus (with semicolon separating the columns)

Row 1: Group 1; Group 1
Row 2: Group 1; 1
Row 3: Group 1; 3
Row 4: Group 1; 100
Row 5: Group 2; Group 2
Row 6: Group 2; 44
Row 7: Group 2; 8
Row 8: Group 3; Group 3
etc.

Then you can have a formula column C such as =IF(OR(A1={"Group 1","Group 3"}),B1,""). There are more flexible ways than the OR statement, such as having a list of the group names you want in a separate sheet, and using ISERROR(MATCH combination.

So the question is how to get that first column to contain the name of the groups. For that, I'd first have your data starting on row 2. Then put this formula in column A, starting on cell A2: =IF(ISNUMBER(B2),A1,B2). This will check column B, and if it's not a number (i.e., a group name), bring it over to column A. If it is a number (data), then copy the cell above (which is the group name).
 

bessieunl

New Member
Joined
May 19, 2015
Messages
11
I'm not sure I understand. Is it something like:

Row 1: Group 1
Row 2: 1
Row 3: 3
Row 4: 100
Row 5: Group 2
Row 6: 44
Row 7: 8
Row 8: Group 3
etc.

And you want to copy items under certain groups.

My main thought is to insert a column to the left and have that column contain the group headings for every row. Thus (with semicolon separating the columns)

Row 1: Group 1; Group 1
Row 2: Group 1; 1
Row 3: Group 1; 3
Row 4: Group 1; 100
Row 5: Group 2; Group 2
Row 6: Group 2; 44
Row 7: Group 2; 8
Row 8: Group 3; Group 3
etc.

Then you can have a formula column C such as =IF(OR(A1={"Group 1","Group 3"}),B1,""). There are more flexible ways than the OR statement, such as having a list of the group names you want in a separate sheet, and using ISERROR(MATCH combination.

So the question is how to get that first column to contain the name of the groups. For that, I'd first have your data starting on row 2. Then put this formula in column A, starting on cell A2: =IF(ISNUMBER(B2),A1,B2). This will check column B, and if it's not a number (i.e., a group name), bring it over to column A. If it is a number (data), then copy the cell above (which is the group name).
Is there a way to use macro to do the copy paste part? Everything else works out just fine, Thanks!
 

AliMass

New Member
Joined
Jun 4, 2015
Messages
14
I was trying to give a solution without a macro, as this would be easier to maintain and more visible to others who use it.

But here is a code I created. The data started in cell A1, and this copies whatever is under Group1 or Group3. It might be better to have a true/false or something in the cell next to each group heading to flag whether it should be copied, otherwise there is higher maintenance on the Select Case statement.


Code:
Option Explicit

Sub separate_groups()


    Dim objCell As Object
    Dim blnOkaytoCopy As Boolean
    
    blnOkaytoCopy = False
    For Each objCell In Range(Cells(1, 1), Cells(65000, 1).End(xlUp))
        If Application.WorksheetFunction.IsNumber(objCell) Then
            'There is a number, so test if we're okay to copy
            If blnOkaytoCopy Then
                objCell.Offset(0, 1) = objCell
            End If
        Else
            'Not a number, so it's a heading. Is this heading one we can copy?
            Select Case objCell
                Case "Group1", "Group3"
                    blnOkaytoCopy = True
                Case Else
                    blnOkaytoCopy = False
            End Select
        End If
    Next objCell
                
End Sub
 

Forum statistics

Threads
1,082,257
Messages
5,364,074
Members
400,778
Latest member
Canadian Sal

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top