VBA to Loop through list of sheets and apply macro

Please help, I've been stuck on this for hours. I have one workbook that is saved as macrobook.xlsm in which column A has a list of worksheet names that I would like to apply a macro to. The worksheets however, are in another workbook called wb05012013.xlsx. The "05012013" refers to a date since the file date is updated and that date value will be in a named cell called "date" in the macrbook.xlsm. I would like the macro to loop through each sheet in wb05012013.xlsx, select all cells and paste values. Then, once the macro comes to a blank cell or end of the list if you will, I'd like it to delete the sheets that are not in the list. Also both of the workbooks are in a shared drive but in the same folder, not sure if that detail makes a difference because the code loops at home, but I get errors at work maybe because the files are stored in shared drives? This is what I have so far...

Please Login or Register  to view this content.

Thanks Holger, your code doesn't loop through the sheets specified in column A. It just stays on the first sheet and doesn't do anything repeatedly.

I've attached two sample workbooks. After the macro comes to a stop, I need it to delete all sheets not listed in Column A, which is in the macrobook. Thanks for your help thus far guys.

Thanks for your help thus far. I haven't constructed the code to delete the sheets as im still new to vba. Where in the code would I start the countif macro and how would I close it. I once tried to add in another macro once the loop comes to an end and got errors regarding "for" missing in the vba.

The new code doesn't seemt to loop through the sheets either. This is what I have to test at home.

Please Login or Register  to view this content.

Aho ok, it does delete the sheets. Now the only issue is my macro doesn't seem to go thorugh each sheet, select all cells, copy and paste as values. When running through the macro with F8, it stays on the same sheet and does nothing but it does delete all the sheets not in the list. This is my code.

Please Login or Register  to view this content.

Originally Posted by HaHoBe

What workbook and sheets are we talking about?

I'm talk about wb2

for some reason at work, set mywb=activeworkbook dispalys without capital letters and affects the macro with an error when it trys to look for the list of sheet names. Any thoughts?

For some reason at work, set mywb=activeworkbook dispalys without capital letters and affects the macro with an error when it trys to look for the list of sheet names. Any thoughts?

Originally Posted by HaHoBe

Hi, hungryhobo,

when recording a macro with the recorder you usually move around in every sheet and switrch sheets.

for some reason at work, set mywb=activeworkbook dispalys without capital letters and affects the macro with an error when it trys to look for the list of sheet names. Any thoughts?

Also your code is selecting column A of each sheet, I need it to select all cells in the sheet and paste values. Thanks so much...the code is nearly complete..

Thank you so much for your patience Holger! The macro works as intended at home! I'll try to figure out what happens at work.

Best Regards

Originally Posted by HaHoBe

With ws2
For Each rngCell In .UsedRange
"If Trim(rngCell.Value) <> vbNullString Then" rngCell.Value = Trim(rngCell.Value) ' The quoted part gives error
Next rngCell
End With

I receive the following error message when testing it at work and all the cells in each sheet still have formulas and haven't been pasted over as values.

run-time error '13':

Type mismatch

Originally Posted by HaHoBe

With ws2
For Each rngCell In .UsedRange
"If Trim(rngCell.Value) <> vbNullString Then" rngCell.Value = Trim(rngCell.Value) ' The quoted part gives error
Next rngCell
End With

I receive the following error message when testing it at work and all the cells in each sheet still have formulas and haven't been pasted over as values.

run-time error '13':

Type mismatch

Duplicate post

http://www.excelforum.com/excel-gene...-mismatch.html