Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Move data to new sheets

Posted on 2015-01-27
8
Medium Priority
?
70 Views
Last Modified: 2015-01-27
In Sheet1 column 'P' there are country names. [i.e. Spain, France,United States, Canada etc.etc.] this has been sorted in order A-Z

I need to copy the entire row for each group of names [including the header] on to a new tab so that each tab contain the header and just the one country.

would appreciate an expert providing me with the VBA code to do this please
0
Comment
Question by:Jagwarman
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 12

Expert Comment

by:FarWest
ID: 40572285
please provide a sample sheet
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40572311
Try this macro

Sub c2sheets()
    Dim cel As Range
    Dim sws As Worksheet
    Dim tws As Worksheet
    Set sws = ActiveSheet
    For Each cel In Range("P2:P" & Range("P" & Rows.Count).End(xlUp).Row)
        If cel.Value <> "" Then
            Set tws = Worksheets.Add(, Worksheets(Worksheets.Count))
            tws.Name = cel.Value
            sws.Range("1:1").Copy tws.Range("1:1")
            cel.EntireRow.Copy tws.Range("2:2")
        End If
    Next cel
End Sub
0
 

Author Comment

by:Jagwarman
ID: 40572397
Hi Saqib Husain, Syed

not quite. What it is trying to do is put each row from sheet 1 onto a new sheet and rename that sheet with the name in column P [i.e. Spain]

So if there are 4 rows in Sheet1 with the name Spain in row P it is trying to name every sheet Spain and each sheet would contain only the header and one row.

What I need it to do is; if there are 4 rows with data for Spain put all 4 rows on that new tab then do same for France, Canada etc.

Thanks
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Expert Comment

by:FarWest
ID: 40572414
Thats why I asked for a sample sheet
is there a problem to upload it
0
 

Author Comment

by:Jagwarman
ID: 40572451
File attached
copy-to-new-sheets.xlsx
0
 
LVL 54

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 40572472
Hi,

pls try

Sub c2sheets()
Set SrcSheet = ActiveSheet
For Idx = 2 To Range("M2:M" & Range("M" & Rows.Count).End(xlUp).Row).Count + 1
    If SrcSheet.Range("M" & Idx).Value <> "" Then
        Set DestSh = Worksheets.Add(, Worksheets(Worksheets.Count))
        DestSh.Name = SrcSheet.Range("M" & Idx).Value
        Idx2 = Idx
        While SrcSheet.Range("M" & Idx).Offset(1) = SrcSheet.Range("M" & Idx)
            Idx = Idx + 1
        Wend
        SrcSheet.Range("1:1").Copy DestSh.Range("1:1")
        SrcSheet.Range(Idx & ":" & Idx2).Copy DestSh.Range("2:2")

    End If
Next
End Sub

Open in new window

Regards
0
 

Author Comment

by:Jagwarman
ID: 40572483
perfect Rgonzo thanks
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40572493
The question says country name in P whereas example shows country name in M

Sub c2sheets()
    Dim cel As Range
    Dim sws As Worksheet
    Dim tws As Worksheet
    Set sws = ActiveSheet
    Application.DisplayAlerts = False
    For Each cel In Range("M2:M" & Range("M" & Rows.Count).End(xlUp).Row)
        If cel.Value <> "" Then
            Set tws = Worksheets.Add(, Worksheets(Worksheets.Count))
            On Error Resume Next
            tws.Name = cel.Value
            If Err Then tws.Delete
            On Error GoTo 0
            Set tws = Sheets(cel.Value)
              sws.Range("1:1").Copy tws.Range("1:1")
            cel.EntireRow.Copy Cells(tws.UsedRange.Rows.Count + 1, 1)
        End If
    Next cel
End Sub

Open in new window

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question