Hi!
This one has been my struggle for a few days now. I have a macro that brings in several sheets from different workbooks (which are batches of clients) and names them after the original workbook. Basically, I have a workbook with worksheets of accrual amounts and the worksheets are named after the batch.
Anyway, I'm trying to add a column with the batch name (worksheet name) and copy it all the way down column J. This seems redundant, but I eventually paste all the batches together, and need this information to pivot.
Below is what I have so far. (The one that's got the ' by it is also something I've been playing with). This brings the sheet name into J2, but nothing I'm trying will copy it all the way down. Any suggestions on how I can add to the below to accomplish this?
Sub JobID()
Dim wsDashboard As Worksheet, ws As Worksheet
Set wsDashboard = Worksheets("Dashboard")
With wsDashboard
.Range("D1").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.Cells(.Rows.Count, 10).End(xlUp).Offset(1, 0).Value = ws.Name
ws.Range("J2").Copy
End If
Next
.Columns(10).AutoFit
End With
End Sub
Thanks!
Rachel
This one has been my struggle for a few days now. I have a macro that brings in several sheets from different workbooks (which are batches of clients) and names them after the original workbook. Basically, I have a workbook with worksheets of accrual amounts and the worksheets are named after the batch.
Anyway, I'm trying to add a column with the batch name (worksheet name) and copy it all the way down column J. This seems redundant, but I eventually paste all the batches together, and need this information to pivot.
Below is what I have so far. (The one that's got the ' by it is also something I've been playing with). This brings the sheet name into J2, but nothing I'm trying will copy it all the way down. Any suggestions on how I can add to the below to accomplish this?
Sub JobID()
Dim wsDashboard As Worksheet, ws As Worksheet
Set wsDashboard = Worksheets("Dashboard")
With wsDashboard
.Range("D1").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.Cells(.Rows.Count, 10).End(xlUp).Offset(1, 0).Value = ws.Name
ws.Range("J2").Copy
End If
Next
.Columns(10).AutoFit
End With
End Sub
Thanks!
Rachel