macro to copy cell in each worksheet to blank worksheet

[houndhen]

Excel 2003 on Windows XP.

I have a workbook that has 40 tabs. I want to copy a cell value from each worksheet and paste it in a column on a new worksheet that I have created. I never was great at writing macros but haven't done any lately.

Each sheet name is the date it was created on - like 01042011. The names generally are a week apart but that may not hold exactly true. I am having a problem accessing each worksheet in a loop like for/next.

Could I use the (name) like 'sheet76' in the vba code?

Should I use something other than for/next?

Thanks,
Harold

 

[cstimart]

Excel 2003 on Windows XP.

I have a workbook that has 40 tabs. I want to copy a cell value from each worksheet and paste it in a column on a new worksheet that I have created. I never was great at writing macros but haven't done any lately.

Each sheet name is the date it was created on - like 01042011. The names generally are a week apart but that may not hold exactly true. I am having a problem accessing each worksheet in a loop like for/next.

Could I use the (name) like 'sheet76' in the vba code?

Should I use something other than for/next?

Thanks,
Harold

instead of calling by the tab name, you could use call them by...(and retrieve the tabname at the same time.

For i = 1 to sheets.count
    Sheets(i).select
    shtName = Sheets(i).Name
Next i

 

[houndhen]

Great! Thanks.

I will have to work on copying the cell value that I want and pasting it on the blank worksheet.

 

[Smitty]

Welcome to the Board!

Here's an example that you should be able to work with:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets<br>            <SPAN style="color:#00007F">If</SPAN> ws.Name <> "Summary" <SPAN style="color:#00007F">Then</SPAN><br>                ws.Range("A1").Copy Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Offset(1)<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> ws<br>End <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

HTH,

 

[houndhen]

Man! Smitty. That is so much more efficient code than what I ended up with. I got mine to work but I like yours better. I will mark this solved.

Thanks,
Harold

 

[Smitty]

I will mark this solved.

No need to do that, just letting everyone know that your issue has been taken care of is all you need to do (there's no "Solved" function on this board and we're not likely to add it).

I'm glad that it worked for you, and please don't hesitate to post back again! That's why we're all here.