Excel: Need to move all items from vendors to own named tab

September 23, 2013 at 11:06:42
Specs: Windows 7
Hello! I would appreciate any help I could get on this issue. I have seen many similar request but can't make any of the macros listed work for me. I have a worksheet with columns A-K and variable rows ranging between 4,000-5,000 rows. I am looking to run a macro that will take all items for each vendor (we have up to 85 vendors each period), move all rows for each vendor to their own sheet. The vendor name is in column D. There are headers in row 1 so D2 would be the first vendor name. And finally, name the tab for each sheet the vendor name. I am working in Excel 2013 Home Business.
Again, any help would be greatly appreciated!

See More: Excel: Need to move all items from vendors to own named tab

Report •

#1
September 23, 2013 at 12:36:20
Do you know how to write VBA code, or at least modify existing code, to meet your needs?

This thread contains a macro to accomplish a similar goal, as well as a link to another thread that contains a different macro to accomplish a similar goal.

http://www.computing.net/answers/of...

If you need help modifying the code, let us know. At a minimum, let us know why you "can't make any of the macros listed work for you".

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
September 23, 2013 at 13:27:16
I have just learned a limited amount by searching and trying. I have looked at the links you sent me, pasted each of them into my spreadsheet and changed the column and ranges to D since that is where my vendor names are in my worksheet. I ran it and nothing is happening. I don't understand the code enough to recognize my errors.

Report •

#3
September 24, 2013 at 08:23:10
re: I don't understand the code enough to recognize my errors

...and we can't help you with the errors unless you tell us what they are.

It wouldn't make sense for us to start from scratch since there is already code available that does what you want. We just need to get it working for your specific circumstance.

Perhaps this will help. I wrote this tutorial back when I was learning VBA. The techniques described can really help you with debugging code and determining how things work and/or why they don't. I use these techniques on a daily basis, not only for my own code, but especially when I'm trying to understand how other people's code does what it does or why it doesn't do what it is supposed to.

http://www.computing.net/howtos/sho...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
September 24, 2013 at 10:08:35
This is the code that I started with and it works but only moves 4 of my 50 vendors to their own tabs. the vendor is in column D of Sheet 1. It seems to be looping but I don't see why it wouldn't run through all of the 4904 rows.

Public Sub MoveToTab()
Dim rngRow As Range
Dim rngCell As Range

On Error GoTo ErrHnd

'use All used rows except first, in 'Sheet 1' Worksheet
With Worksheets("Sheet 1").UsedRange.Offset(1, 0) _
.Resize(Worksheets("Sheet 1").UsedRange.Rows.Count - 1, _
Worksheets("Sheet 1").UsedRange.Columns.Count)
'loop through all rows
For Each rngRow In .Rows
'test if tab exists
On Error Resume Next
If Not Worksheets(rngRow.Range("D2").Text).Name <> "" Then
On Error GoTo ErrHnd
'No Tab of this name - so create one and copy row
Worksheets.Add After:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Name = rngRow.Range("D2").Text
rngRow.Copy Destination:=Worksheets(rngRow.Range("D2").Text).Range("A1")
Else
On Error GoTo ErrHnd
'worksheet exists
'copy row to end of used range
rngRow.Copy Destination:=Worksheets(rngRow.Range("D2").Text).Range("A1") _
.Offset(Worksheets(rngRow.Range("D2").Text).UsedRange.Rows.Count, 0)
End If
Next rngRow
End With

Exit Sub

'error handler
ErrHnd:
Err.Clear
End Sub


Report •

#5
September 24, 2013 at 13:24:09
First, a posting tip...

Please click on the following line and read the instructions found via that link before posting VBA code in this forum. Thanks!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
September 24, 2013 at 13:53:12
Public Sub MoveToTab()
Dim rngRow As Range
Dim rngCell As Range

On Error GoTo ErrHnd

'use All used rows except first, in 'Source' Worksheet
With Worksheets("Source").UsedRange.Offset(1, 0) _
    .Resize(Worksheets("Source").UsedRange.Rows.Count - 1, _
    Worksheets("Source").UsedRange.Columns.Count)
    'loop through all rows
    For Each rngRow In .Rows
        'test if tab exists
        On Error Resume Next
        If Not Worksheets(rngRow.Range("B1").Text).Name <> "" Then
            On Error GoTo ErrHnd
            'No Tab of this name - so create one and copy row
            Worksheets.Add After:=Worksheets(Worksheets.Count)
            Worksheets(Worksheets.Count).Name = rngRow.Range("B1").Text
            rngRow.Copy Destination:=Worksheets(rngRow.Range("B1").Text).Range("A1")
            Else
            On Error GoTo ErrHnd
            'worksheet exists
            'copy row to end of used range
            rngRow.Copy Destination:=Worksheets(rngRow.Range("B1").Text).Range("A1") _
                .Offset(Worksheets(rngRow.Range("B1").Text).UsedRange.Rows.Count, 0)
        End If
    Next rngRow
End With

Exit Sub

'error handler
ErrHnd:
Err.Clear
End Sub


Report •

Ask Question