Code to loop through ws only works on active sheet

[rey06]

Hi!

Hoping this is an easy one. Trying to run the below code on all sheets minus the one called "DASHBOARD". However, it currently only runs on the active sheet (which is the DASHBOARD sheet usually). Can this sort of thing not be used to loop through sheets or am I missing something? I'm literally just pulling the sheet name into all the rows with data into column J of sheets not named "DASHBOARD", and then just minor editing so if there's a better way, I am all ears!

Sub jobID()


Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "DASHBOARD" Then


Range("I1").Select
Selection.Copy
Range("J1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Job ID"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=MID(CELL(""filename"",R[-1]C[-9]),FIND(""]"",CELL(""filename"",R[-1]C[-9]))+1,255)"
Range("J2").Select
Selection.Copy
Range("I2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Columns("J:J").EntireColumn.AutoFit
Columns("J:J").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Application.Goto Reference:="R1C1"


End If
Next ws


End Sub

TIA!
:)

[Paul_Hossler]

Really not sure what you're doing, but

1. Range("I1") refers to the ActiveSheet, whereas ws.Range("I1") refers to the sheet ws



2. Don't need to select something to act on or with it



ws. Range("I1").Copy ws.Range("J1")

ActiveSheet.Paste ' <<<< this would most likely NOT be the ws worksheet



and something like



ws.Range("J2").FormulaR1C1 = "=MID(CELL(""filename"",R[-1]C[-9]),FIND(""]"",CELL(""filename"",R[-1]C[-9]))+1,255)"

[rey06]

Really not sure what you're doing, but




Trying to loop through each spreadsheet and just bringing in the sheet name in column J of each sheet.

[Paul_Hossler]

This might be a little closer to what you want


Not sure if I'm reading this correctly:


just bringing in the sheet name in column J of each sheet.





Option Explicit


Sub BuildDashboard()
Dim wsDashboard As Worksheet, ws As Worksheet

Set wsDashboard = Worksheets("Dashboard")

With wsDashboard
.Range("J1").Value = "Job ID"

For Each ws In Worksheets
If Not ws Is wsDashboard Then
.Cells(.Rows.Count, 10).End(xlUp).Offset(1, 0).Value = ws.Name
End If
Next

.Columns(10).AutoFit
End With




End Sub

[snb]

You'd betteer read this first: Excel VBA Programming For Dummies by John Walkenbach | 9781119077398 | Paperback | Barnes & Noble (http://www.barnesandnoble.com/w/excel-vba-programming-for-dummies-john-walkenbach/1101874584)

[rey06]

That's closer to what I want. This brings in all the sheet names to the DASHBOARD sheet, but I don't want anything there. It doesn't do anything with any other sheets which is where I want the Job ID (sheet name) brought into Column J.

I'm going to play around with the code you provided a little bit. I can usually figure this out by recording and editing, but the information you provided me above tells me that doesn't work for looping through sheets so I appreciate your insight on that!

[p45cal]

ws.Cells(Rows.Count, 10).End(xlUp).Offset(1, 0).Value = ws.Name

[Paul_Hossler]

which is where I want the Job ID (sheet name) brought into Column J.

You didn't say that you wanted "Job1234" which is the name of worksheet Job1234 brought into column J2 on worksheet Job1234



ws.Range("J2") .Value = ws.Name

[rey06]

Thank you both, Paul and p45cal! I've been able to piece together the information from both of you, and it's almost working as I need it to! Currently, on the other sheets, I'm getting "JobID" in J1, and the sheet name in J2, but I need the job ID in every row of data in column J. (Sorry if I wasn't clear on what I wanted there!) Do I need to add something like a Selection, Selection.End(x1Up)? I've tried a few different things to no avail.

This is what I've come up with so far based on what you've told me. Sorry if I'm not piecing this together right - looping isn't something I'm familiar with. I can usually piece these things together with past posts by others or recording and editing, but not in this case apparently.

Your help thus far has been greatly appreciated!



Option Explicit




Sub BuildDashboard()
Dim wsDashboard As Worksheet, ws As Worksheet

Set wsDashboard = Worksheets("Dashboard")

With wsDashboard
.Range("J1").Value = "Job ID"

For Each ws In Worksheets
If Not ws Is wsDashboard Then
ws.Range("J1").Value = "JobID"
ws.Range("J2").Value = ws.Name
ws.Cells(Rows.Count, 10).End(xlUp).Offset(1, 0).Value = ws.Name
End If
Next

.Columns(10).AutoFit
End With

End Sub

[Paul_Hossler]

I'm getting "JobID" in J1, and the sheet name in J2, but I need the job ID in every row of data in column J.


Sound contradictory to me:think:

How about attaching a sample workbook showing what you want the result to look like

[rey06]

Sound contradictory to me:think:

How about attaching a sample workbook showing what you want the result to look like

Yeah, it kind of is. But in the end, these sheets get combined so I can pivot which is why I need it in every row. It'll look odd on each individual sheet, but it serves a purpose. :)

[rey06]

I've gotten a little further with this today (not much obviously) but I think it's more straightforward what I need now. That value in J2 that's copied needs to be copied down the entire column so long as there is data in the row. I've been try to do something like selecting I2, going to the bottom of the dataset, doing an offset for one column to the right, then filling from the bottom to the top. I've had luck doing that with other macros, but this one being a loop is giving me major problems. But any help in finalizing this one is much appreciated!

I know I've probably overcomplicated this one to death and there is probably a way to simplify, but I just record, edit and go, and if it runs pretty fast, it's good enough for me!



Option Explicit




Sub JobID()
Dim wsDashboard As Worksheet, ws As Worksheet

Set wsDashboard = Worksheets("Dashboard")

With wsDashboard
.Range("J1").Value = "JobID"

For Each ws In Worksheets
If Not ws Is wsDashboard Then
ws.Range("I1").Copy
ws.Range("J1").PasteSpecial xlPasteFormats
ws.Range("J1").Value = "JobID"
ws.Range("J2").Value = ws.Name
ws.Range("J2").Copy

End If
Next

.Columns(10).AutoFit
End With
End Sub

[snb]

I know I've probably overcomplicated this one to death and there is probably a way to simplify, but I just record, edit and go, and if it runs pretty fast, it's good enough for me!

If that is your attitude I am not inclined to suggest any improvement.