Solved

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

Posted on 2011-03-22
7
1,066 Views
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
0
Comment
Question by:decisionfocus
  • 4
  • 3
7 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35186960
Format Sheet Missing.

Sid
0
 

Author Comment

by:decisionfocus
ID: 35186965
Hi Sid,

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

Br Jonas
Solar-Weekly-0.2.xls
0
 
LVL 30

Expert Comment

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

Just cleaning it and testing it :)

Sid
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 30

Accepted Solution

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

Sid

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.Range("A36:X36")
        ws2.Rows("35:36").EntireRow.Hidden = True
    Next i
    
LetsContinue:
    Sheets("Format").Visible = False
    Application.ScreenUpdating = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

Open in new window

Solar-Weekly-0.1.xls
0
 

Author Comment

by:decisionfocus
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..
0
 

Author Closing Comment

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

Expert Comment

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

Sure not a problem :)

Sid
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

912 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now