R
ryguy7272
I am stuck on something that shouldn't be too hard...but seems hard right
now. I am working on a macro that lists all sheets in my workbook, except
for three specific sheets, and then I wanted to list the last user row on
each sheet, so that it corresponds with each name in the list (the names come
from the tabs in the workbook). This is what I have so far:
Sub SummarySht()
Application.DisplayAlerts = False
Dim sht As Worksheet
Dim I As Long
Dim bWrite As Boolean
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("Summary").Delete
On Error GoTo 0
Application.DisplayAlerts = True
Set Basebook = ThisWorkbook
Set Newsht = Basebook.Worksheets.Add
Newsht.Name = "Summary"
Sheets("Summary").Select
n = Worksheets.Count
For I = 1 To n
sht = Worksheets(I).Name
If sht = "Summary" Or sht = "C2_UnionQuery" Or sht = "Summary-Sheet"
Then
Else
ActiveCell.Value = Sheets("Summary").Range("A3")
ActiveCell.Offset(1, 0).Select
End If
Next
End Sub
The macro fails on this line:
sht = Worksheets(I).Name
The message that I get is ‘Object Variable with Block Variable not Set’.
The code looks right to me, so I’m not sure why it’s saying this.
Also, I am still working on a way of copying the last used row from each
sheet in the book, except for "Summary", "C2_UnionQuery", and "Summary-Sheet".
I think the 'last used row' part will be something like this:
Dim LastRow As Long
Dim sht As Worksheet
For Each sht In Worksheets
If (sht.Name) <> "Summary" and (sht.Name) <> "C2_UnionQuery" And
(sh.Name) <> "Summary-Sheet" Then
sht.Activate
Chng = Range("A6").Value
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Cells(LastRow, "A").Activate
sh.Cells(Rows.Count, "A").End(xlUp).EntireRow.Copy = True
Sheets("Summary").Activate
ActiveCell.Value = Sheets("Summary").Range("A3")
ActiveCell.Offset(2, 1).Select
ActiveCell.Paste
Next sht
....but it is not quite right. The last row on each of the sheets should
correspond to the names (from the tabs) listed in the 'Summary' sheet. Can
someone please give me some assistance?
Thanks,
Ryan---
now. I am working on a macro that lists all sheets in my workbook, except
for three specific sheets, and then I wanted to list the last user row on
each sheet, so that it corresponds with each name in the list (the names come
from the tabs in the workbook). This is what I have so far:
Sub SummarySht()
Application.DisplayAlerts = False
Dim sht As Worksheet
Dim I As Long
Dim bWrite As Boolean
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("Summary").Delete
On Error GoTo 0
Application.DisplayAlerts = True
Set Basebook = ThisWorkbook
Set Newsht = Basebook.Worksheets.Add
Newsht.Name = "Summary"
Sheets("Summary").Select
n = Worksheets.Count
For I = 1 To n
sht = Worksheets(I).Name
If sht = "Summary" Or sht = "C2_UnionQuery" Or sht = "Summary-Sheet"
Then
Else
ActiveCell.Value = Sheets("Summary").Range("A3")
ActiveCell.Offset(1, 0).Select
End If
Next
End Sub
The macro fails on this line:
sht = Worksheets(I).Name
The message that I get is ‘Object Variable with Block Variable not Set’.
The code looks right to me, so I’m not sure why it’s saying this.
Also, I am still working on a way of copying the last used row from each
sheet in the book, except for "Summary", "C2_UnionQuery", and "Summary-Sheet".
I think the 'last used row' part will be something like this:
Dim LastRow As Long
Dim sht As Worksheet
For Each sht In Worksheets
If (sht.Name) <> "Summary" and (sht.Name) <> "C2_UnionQuery" And
(sh.Name) <> "Summary-Sheet" Then
sht.Activate
Chng = Range("A6").Value
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Cells(LastRow, "A").Activate
sh.Cells(Rows.Count, "A").End(xlUp).EntireRow.Copy = True
Sheets("Summary").Activate
ActiveCell.Value = Sheets("Summary").Range("A3")
ActiveCell.Offset(2, 1).Select
ActiveCell.Paste
Next sht
....but it is not quite right. The last row on each of the sheets should
correspond to the names (from the tabs) listed in the 'Summary' sheet. Can
someone please give me some assistance?
Thanks,
Ryan---