英文:
Get last value of a specific column from all sheet Excel VBA
问题
我有一个工作簿，有很多工作表（至少100多个工作表）。
所有工作表都具有相同的设计和格式。每个工作表都有一个名为L列的列，在这里我计算了结果。并且在L列的末尾，我对L列的值进行了小计。
我使用下面的VBA代码获取了所有工作表的名称。
主页工作表（汇总工作表）
Sub GetAllSheetNames()
For i = 1 To ThisWorkbook.Sheets.Count
ActiveSheet.Cells(i, 1) = i
ActiveSheet.Cells(i, 2) = ThisWorkbook.Sheets(i).Name
Next i
With ActiveSheet
.Rows(1).Insert
.Cells(1, 1) = "INDEX"
.Cells(1, 1).Font.Bold = True
.Cells(1, 2) = "SHEET NAME"
.Cells(1, 2).Font.Bold = True
.Columns("A:B").AutoFit
End With
End Sub
在我的汇总工作表中，列A有索引标题，列B有工作表名称标题。在列C中，我需要对所有工作表的L列的最后一个值进行小计。
我尝试了上面提到的代码。
在运行答案中提供的代码后出现错误。
英文:
I have a workbook. with lots of sheets (at least 100+ sheets).
all sheet has same design and format. every sheet has L column, where I calculated the results. and at the end of the L column I subtotal the L column values.
I got all sheet names with the below vba code.
Home Sheet (Summary Sheet)
Sub GetAllSheetNames()
For i = 1 To ThisWorkbook.Sheets.Count
ActiveSheet.Cells(i, 1) = i
ActiveSheet.Cells(i, 2) = ThisWorkbook.Sheets(i).Name
Next i
With ActiveSheet
.Rows(1).Insert
.Cells(1, 1) = "INDEX"
.Cells(1, 1).Font.Bold = True
.Cells(1, 2) = "SHEET NAME"
.Cells(1, 2).Font.Bold = True
.Columns("A:B").AutoFit
End With
End Sub
where my summary sheet has column A with index header and column B has sheet name header.
and in the column C, I need subtotals of all L columns last values from all sheets.
I tried code mentioned above.
> Got Error After Running the code given in answer.
答案1
得分: 1
请尝试这样做：
子 获取所有工作表名称()
Dim iR As Long, sht As Worksheet
With Sheets("租金汇总")
.Cells.Clear
.Range("a1:c1").Value = Array("索引", "工作表名称", "小计")
.Range("a1:c1").Font.Bold = True
iR = 2
对于 Each sht In ThisWorkbook.Sheets
如果 sht.Name <> .Name 然后
.Cells(iR, 1) = iR - 1
.Cells(iR, 2) = sht.Name
.Cells(iR, 3) = sht.Cells(Rows.Count, "L").End(xlUp).Value
iR = iR + 1
结束 如果
下一个
.Columns("A:C").AutoFit
结束 With
结束 子
英文:
Please try it.
Sub GetAllSheetNames()
Dim iR As Long, sht As Worksheet
With Sheets("Rent Summary")
.Cells.Clear
.Range("a1:c1").Value = Array("INDEX", "SHEET NAME", "SUBTOTAL")
.Range("a1:c1").Font.Bold = True
iR = 2
For Each sht In ThisWorkbook.Sheets
If sht.Name <> .Name Then
.Cells(iR, 1) = iR - 1
.Cells(iR, 2) = sht.Name
.Cells(iR, 3) = sht.Cells(Rows.Count, "L").End(xlUp).Value
iR = iR + 1
End If
Next
.Columns("A:C").AutoFit
End With
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论