Excel 2007/2010: traverse data range, create new sheet per row, copy current row into new sheet

Posted on 2011-03-22
Last Modified: 2012-05-11
Hi - got a problem i hope you'd be able to help me solve.

I have  workbook (attached here) with two sheets, "Data Input" & "Format" - "Format" is a hidden sheet. In the "Data Input" sheet I have 1 header row (row 1) and X data rows (rows 2-X). I need to for each data-row:

- Create a copy of the "Format" sheet
- Copy the current data row from "Data Input" into the newly created copy of "Format" in A36:X36
- set the name of the newly created sheet to the value in cell B36
- Hide range A35:X36 in the newly created sheet

When done for all data-rows i need to

- Hide the "Data Input" sheet

Any help would be greatly appreciated!

Br Jonas Solar-Weekly-0.1.xls
Question by:decisionfocus
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 30

Expert Comment

ID: 35186960
Format Sheet Missing.


Author Comment

ID: 35186965
Hi Sid,

Sorry - have attached new file. The format sheet is hidden but unprotected.

Br Jonas
LVL 30

Expert Comment

ID: 35186969
The code is almost ready... :)

Just cleaning it and testing it :)

Industry Leaders: 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 30

Accepted Solution

SiddharthRout earned 500 total points
ID: 35187006
Ok here is the file. Please run the macro Sample in Module 1.


Code Used

Sub Sample()
    Dim i As Long, LastRow As Long
    Dim ws1 As Worksheet, ws2 As Worksheet
    On Error GoTo Whoa
    Application.ScreenUpdating = False
    Set ws1 = Sheets("Data Input")
    LastRow = ws1.Range("A" & Rows.Count).End(xlUp).Row
    Sheets("Format").Visible = True
    For i = 2 To LastRow
        Sheets("Format").Copy After:=Sheets(Sheets.Count)
        Set ws2 = ActiveSheet
        ws2.Name = ws1.Range("B" & i).Value
        ws1.Range("A" & i & ":X" & i).Copy _
        ws2.Rows("35:36").EntireRow.Hidden = True
    Next i
    Sheets("Format").Visible = False
    Application.ScreenUpdating = True
    Exit Sub
    MsgBox Err.Description
    Resume LetsContinue
End Sub

Open in new window


Author Comment

ID: 35187071
That is absolutely AMAZING - thanks so much for your help! I actually have another question, but i prefer to create it as a new question so i can give more points for it - hope you'll be able to take a quick look at it as well..

Author Closing Comment

ID: 35187075
Amazingly quick & correc answer - best help imaginable!
LVL 30

Expert Comment

ID: 35187124
>>>hope you'll be able to take a quick look at it as well..

Sure not a problem :)


Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

733 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