Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
return value from range of worksheets | Excel Worksheet Functions | |||
Compare 2 Worksheets and return differences in a third. | Excel Worksheet Functions | |||
How can i compare particular columns of two worksheets and get a return | Excel Programming | |||
matching two worksheets and return to different value | Excel Worksheet Functions | |||
Function to Return another Worksheets Name | Excel Worksheet Functions |