pirdop
07-29-2010, 02:45 AM
Hi all,
I need to develop a macro that copies (just the values not formulas)
3 separate groups of specific sheets to 3 new workbooks.
for example sheets X,Y,Z have to be copied to new workbook named
Original's workbook name.X.xls and A,B,C, to Original's workbook name.A.xls , K,L,M to Original's workbook name.K.xls respectively
I've found the following code in the knowledge base, which does exactly what I want but just for ne group (array )
*t*p://**w.vbaexpress.com/kb/getarticle.php?kb_id=359
apparently I cannot yet post links so just replace the *
would please advice how to modify the code in order to operate with
3 arrays?
I'm new to VBA and don't clearly understand the following part
For Each ws In ActiveWorkbook.Worksheets
ws.Cells.Copy
ws.[A1].PasteSpecial Paste:=xlValues
ws.Cells.Hyperlinks.Delete
Application.CutCopyMode = False
Cells(1, 1).Select
ws.Activate
Next ws
Cells(1, 1).Select
or mmore precisely how this is related to the Sheets(array("X","Y","Z") ?
I mean what cells it paste in the new workbook considering that it is not referring to the array, to my understanding!?
Which of course is wrong as the code works - so if anyone would advice, explain it to me : pray2: !!
I have to learn the VBA, and this is the best way, I guess
Thank you in advance!
P.S. the renaming pattern I solved it that way
NewName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) + ".XYZ"
where .XYZ could be a variable or to call it as a string directly from the arrays once i understand how the above code works
I need to develop a macro that copies (just the values not formulas)
3 separate groups of specific sheets to 3 new workbooks.
for example sheets X,Y,Z have to be copied to new workbook named
Original's workbook name.X.xls and A,B,C, to Original's workbook name.A.xls , K,L,M to Original's workbook name.K.xls respectively
I've found the following code in the knowledge base, which does exactly what I want but just for ne group (array )
*t*p://**w.vbaexpress.com/kb/getarticle.php?kb_id=359
apparently I cannot yet post links so just replace the *
would please advice how to modify the code in order to operate with
3 arrays?
I'm new to VBA and don't clearly understand the following part
For Each ws In ActiveWorkbook.Worksheets
ws.Cells.Copy
ws.[A1].PasteSpecial Paste:=xlValues
ws.Cells.Hyperlinks.Delete
Application.CutCopyMode = False
Cells(1, 1).Select
ws.Activate
Next ws
Cells(1, 1).Select
or mmore precisely how this is related to the Sheets(array("X","Y","Z") ?
I mean what cells it paste in the new workbook considering that it is not referring to the array, to my understanding!?
Which of course is wrong as the code works - so if anyone would advice, explain it to me : pray2: !!
I have to learn the VBA, and this is the best way, I guess
Thank you in advance!
P.S. the renaming pattern I solved it that way
NewName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) + ".XYZ"
where .XYZ could be a variable or to call it as a string directly from the arrays once i understand how the above code works