add

[lisat76]

i have to make into a database no big deal, except that
the people who made the spreadsheets set up different worksheets for each employees location.
example they have one worksheet that has all ohio employees, one for all ky etc each worksheet is named after the location. they have 2
spreadsheets each of them has over 50 locations! the worksheets are set up pretty good except they don't have a separate column for location they just depend on the worksheet name. Now i have to add a column for each location and add that location title
to each row yuck!
I wondered how i could create a macro in excel that would automaticlly add a column and put the sheet name in each row?
here is my current macro that combines all worksheets

Sub Combine()
    Dim J As Integer

    On Error Resume Next
    Sheets(1).Select
    Worksheets.Add ' add a sheet in first place
    Sheets(1).Name = "Combined"
  

    ' copy headings
    Sheets(2).Activate
    Range("A1").EntireRow.Select
    Selection.Copy Destination:=Sheets(1).Range("A1")

    ' work through sheets
    For J = 2 To Sheets.Count ' from sheet 2 to last sheet
        Sheets(J).Activate ' make the sheet active
        Range("A1").Select
        Selection.CurrentRegion.Select ' select all cells in this sheets

        ' select all lines except title
        Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

        ' copy cells selected in the new sheet on last line
        Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
    Next
End Sub

 

[lisat76]

sorry about my title should have said add worksheet name to rows sorry...

 

[Bong]

It's easy enough but I'm not sure what exactly you're doing. The active worksheet's name is activesheet.name

_________________
Bob Rashkin

 

[lisat76]

ok for instance there is one worksheet nameed Ohio
it has first name last name address etc for state
they did not put a column instead they just assume by looking at the worksheet name you'll know it is for ohio.
I want to combine all the worksheets into one but right now if i do that i won't know who is in what state since there is no column for that.
If i combine them i would get
joe jones 1234 my street
I want to add a column and in that column it will populate
based on the worksheet name
so for the combined sheet i would
get joe jones 1234 my street ohio
mike jones 2345 my street ky

 

[Bong]

So, if I understand you correctly, you start with a workbook (only one workbook) that has many sheets. You want to add another sheet where you'll be combining data from the previously existing sheets, including a column naming the sheet of origin.

So first you want to make sure you add the new sheet so that it is the last in the sheets collection:
sheets.add after:=sheets(sheets.count)
Now you can interate through the sheets collection, up to but not including that last one:
for each s in sheets
if s.name <> sheets(sheets.count).name then
<put s.name> in the column you want>
....
end if
next

_________________
Bob Rashkin

 

[lisat76]

yes that is correct, i'll see if that works

 

[lisat76]

actually how do i add the s.name to say column p?

 

[Bong]

Assuming you know your row number, cells(<row number>,16)=s.name ("p" being column 16).

_________________
Bob Rashkin

 

[lisat76]

i actually wanted this added to every row that has data

 

[Bong]

i actually wanted this added to every row that has data

Are you talking about the target sheet or the origin sheets? Assuming the former, how are those rows populated? The easiest way to add the column-P data would be at the time the rows are built.

_________________
Bob Rashkin

 

[Bong]

In fact, if you're talking about adding the sheet name on the origin sheets, I'd build a simple function:

Function sname()
    sname = ActiveSheet.Name
End Function

and then use it (=sname()) in the column where you want the sheet name.

_________________
Bob Rashkin