Move every individual row into a new (individual) worksheet

[ivovandenberk]

Hi,

I am completely new to macros and VB, so I don't really know how to do this but this is what I need.

I have a worksheet (let's call it "data") with about 1700 rows and about 30 columns. I need to get every individual row into a new individual worksheet. So that afterwards I have 1700 worksheets with one row in every worksheet. The first row (from the 'data' worksheet) would need to be copied to every worksheet as well as that is were the variable names are written.

I hope the example explains it further:


data worksheet
first name last name zipcode city state country etc
aasda asdasdas 9292 aksd alkjsd asdasd asdasd
asdg lkasjdalks 8288 alkjsd lskfdg agdsad gzxca
asdfg asladklas 2411 asdla l;;xca ;aslda;s a;lsd;a

worksheet 1
first name last name zipcode city state country etc
aasda asdasdas 9292 aksd alkjsd asdasd asdasd

worksheet 2
first name last name zipcode city state country etc
asdg lkasjdalks 8288 alkjsd lskfdg agdsad gzxca

worksheet 3
first name last name zipcode city state country etc
asdfg asladklas 2411 asdla l;;xca ;aslda;s a;lsd;a

etc...

Thanks

 

[John Davis]

Hi,

I am completely new to macros and VB, so I don't really know how to do this but this is what I need.

I have a worksheet (let's call it "data") with about 1700 rows and about 30 columns. I need to get every individual row into a new individual worksheet. So that afterwards I have 1700 worksheets with one row in every worksheet. The first row (from the 'data' worksheet) would need to be copied to every worksheet as well as that is were the variable names are written.

I hope the example explains it further:


data worksheet
first name last name zipcode city state country etc
aasda asdasdas 9292 aksd alkjsd asdasd asdasd
asdg lkasjdalks 8288 alkjsd lskfdg agdsad gzxca
asdfg asladklas 2411 asdla l;;xca ;aslda;s a;lsd;a

worksheet 1
first name last name zipcode city state country etc
aasda asdasdas 9292 aksd alkjsd asdasd asdasd

worksheet 2
first name last name zipcode city state country etc
asdg lkasjdalks 8288 alkjsd lskfdg agdsad gzxca

worksheet 3
first name last name zipcode city state country etc
asdfg asladklas 2411 asdla l;;xca ;aslda;s a;lsd;a

etc...

Thanks

Maybe, but be sure to test first:

Sub ivovandenberk()
Dim i As Long
Dim lr As Long
Dim currentsheet As Worksheet

currentsheet = ActiveSheet

lr = Cells(Rows.Count, 1).End(3).Row

For i = lr To 2 Step -1

    Sheets.Add.Name = "New Sheet"
    
    currentsheet.Activate
    
    Range("A1").EntireRow.Copy Sheets("NewSheet").Range("A1")
    Range("A" & i).EntireRow.Copy Sheets("NewSheet").Range("A" & Rows.Count).End(3)(2)
    Sheets("NewSheet").Name = Sheets("NewSheet").Range("A2").Value

Next i

End Sub

 

[ivovandenberk]

Thanx for that. It didn't work however.

I tried to run it, but on the line: "currentsheet = ActiveSheet" i get the error: object variable or with block variable not set (error 91). Any idea what the problem might be? Thanx!

 

[Weaver]

should be

[B][I][U]set[/U][/I][/B] currentsheet = activesheet

 

[ivovandenberk]

Although that got me a little further it still doesn't work. Although a new sheet does get created I am Getting a new error: "the subscript is out of range (error 9)" on the line: Range("A1").EntireRow.Copy Sheets("NewSheet").Range("A1"). Thanks for the links. Going through them right now :D

 

[John Davis]

Although that got me a little further it still doesn't work. Although a new sheet does get created I am Getting a new error: "the subscript is out of range (error 9)" on the line: Range("A1").EntireRow.Copy Sheets("NewSheet").Range("A1"). Thanks for the links. Going through them right now :D

The subscript out of range was caused by my errors in the coding. Here is a tested revision.

Sub ivovandenberk()
Dim i As Long
Dim lr As Long
Dim currentsheet As Worksheet

Set currentsheet = ActiveSheet

lr = Cells(Rows.Count, 1).End(3).Row

For i = lr To 2 Step -1

    Sheets.Add.Name = "New Sheet"
    
    currentsheet.Activate
    
    Range("A1").EntireRow.Copy Sheets("New Sheet").Range("A1")
    Range("A" & i).EntireRow.Copy Sheets("New Sheet").Range("A" & Rows.Count).End(3)(2)
    Sheets("New Sheet").Name = Sheets("New Sheet").Range("A2").Value

Next i

End Sub

 

[ivovandenberk]

Fantastic! that worked like a charm! Thanks for making me look good to my boss.

 

[John Davis]

Fantastic! that worked like a charm! Thanks for making me look good to my boss.

You're welcome. Thanks for the feedback. Hope you get a raise as well as look good!