cycle sheets and capture data

jose001

Board Regular
Joined
Mar 27, 2006
Messages
103
Thanks for the help everyone over the few days! I still have one more little query though if anyone can help me out. Lewiy has given me some code http://www.mrexcel.com/board2/viewtopic.php?p=1370688#1370688 which will cycle through my sheets and pull out some data from specific cells. Sadly I cant get it to work though. I'm trying to get the code to go through all of the sheets and pull out the info from cell A2 and pop it in cell B6 of my summary sheet and then cell A3 and pop that into C6. The thing is, because there are many sheets I will need this process to repeat filling each row from B6 down with the information from the other sheets. Help!!
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
something like this maybe?
Code:
Sub TransferData()
Dim i As Long: i = 6
For Each sh In Worksheets
    If sh.Name <> "Summary" Then
        Worksheets("Summary").Range("B" & i).Value = sh.Range("A2").Value
        Worksheets("Summary").Range("C" & i).Value = sh.Range("A3").Value
        i = i + 1
    End If
Next sh
End Sub
 

jose001

Board Regular
Joined
Mar 27, 2006
Messages
103
Hi Iggy,

I've copied this code and attached it to a button but it's not working :( any ideas?
 

jose001

Board Regular
Joined
Mar 27, 2006
Messages
103
Hi Iggy,

I've copied this code and attached it to a button but it's not working :( any ideas?
 

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647

ADVERTISEMENT

do you have a sheet named Summary?
 

jose001

Board Regular
Joined
Mar 27, 2006
Messages
103
I did, but I've changed it to index now but I did update the ref in your code. I basically have two main sheets 'index' and 'actions'. the index tab has a useform which takes the data, opens a new sheet and calls it whatever is in the first field of the userform and then inserts the rest of the data. I have a button on the actions sheet which I would like, when pressed, to go into every sheet bar index and actions, look in cells A2 and B2 and present the information in a table in actions. The sheets will be deleted and added on a regular basis.

Jose
 

Watch MrExcel Video

Forum statistics

Threads
1,130,102
Messages
5,640,115
Members
417,126
Latest member
Jeffman52

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top