macro to return value of worksheets
hi. how do i make a macro that returns the value of the cell next to the word
"total" from all worksheets which are listed in column A? i got to make the macro that lists all worksheets in that workbooks into a summary worksheet. Now i would like to find the value of the total amount in each worksheet and return it to the next column of their corresponding sheet name lists? the first macro is like this: Sub auto_open() Sheets("summary").Activate x = 1 Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets Cells(x, 1).Value = ws.Name x = x + 1 Next ws Cells(1, 2).Value = x - 1 End Sub |
macro to return value of worksheets
Assume that the value is to the right of Total and that
"Total" is proper case, only appears once in each sheet and could be located anywhere on the sheet. Sub getData() Dim ws As Range Dim c As Range SName = ActiveSheet.Name lr = Cells(Rows.Count, 1).End(xlUp).Row For Each ws In Sheets(SName).Range("A2:A" & lr) If Not ws Is Nothing Then fAddr = ws.Address fName = ws.Value Set c = Worksheets(fName).Cells.Find("Total", LookIn:=xlValues) If Not c Is Nothing Then c.Offset(0, 1).Copy Sheets(SName).Range(fAddr).Offset(0, 1) End If End If Next End Sub "Chris" wrote: hi. how do i make a macro that returns the value of the cell next to the word "total" from all worksheets which are listed in column A? i got to make the macro that lists all worksheets in that workbooks into a summary worksheet. Now i would like to find the value of the total amount in each worksheet and return it to the next column of their corresponding sheet name lists? the first macro is like this: Sub auto_open() Sheets("summary").Activate x = 1 Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets Cells(x, 1).Value = ws.Name x = x + 1 Next ws Cells(1, 2).Value = x - 1 End Sub |
macro to return value of worksheets
i tried the macro, and it returns a #ref! error on the cell.... yup, the
value is to the right of the word "total, and it only appears once in each sheet and could be located anywhere in each sheet.... "JLGWhiz" wrote: Assume that the value is to the right of Total and that "Total" is proper case, only appears once in each sheet and could be located anywhere on the sheet. Sub getData() Dim ws As Range Dim c As Range SName = ActiveSheet.Name lr = Cells(Rows.Count, 1).End(xlUp).Row For Each ws In Sheets(SName).Range("A2:A" & lr) If Not ws Is Nothing Then fAddr = ws.Address fName = ws.Value Set c = Worksheets(fName).Cells.Find("Total", LookIn:=xlValues) If Not c Is Nothing Then c.Offset(0, 1).Copy Sheets(SName).Range(fAddr).Offset(0, 1) End If End If Next End Sub "Chris" wrote: hi. how do i make a macro that returns the value of the cell next to the word "total" from all worksheets which are listed in column A? i got to make the macro that lists all worksheets in that workbooks into a summary worksheet. Now i would like to find the value of the total amount in each worksheet and return it to the next column of their corresponding sheet name lists? the first macro is like this: Sub auto_open() Sheets("summary").Activate x = 1 Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets Cells(x, 1).Value = ws.Name x = x + 1 Next ws Cells(1, 2).Value = x - 1 End Sub |
All times are GMT +1. The time now is 04:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com