Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each workbook in Folder A Copy Values & Format of each Sheet2...
For each workbook in Folder A
Copy Values & Format (not formulas) of every Sheet2 to Folder B new workbook, Sheet2 (same workbook name from Folder A) Next Help appreciated. Celeste |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each workbook in Folder A Copy Values & Format of each Sheet2...
Hi Celeste,
See Ron de Bruin's sample code at: Merge data from all workbooks in a folder (1) http://www.rondebruin.nl/copy3.htm and Merge data from all workbooks in a folder (2) http://www.rondebruin.nl/fso.htm See also Ron's RDBMerge Add-in: Merge data from all workbooks in a folder http://www.rondebruin.nl/merge.htm --- Regards. Norman "u473" wrote in message ... For each workbook in Folder A Copy Values & Format (not formulas) of every Sheet2 to Folder B new workbook, Sheet2 (same workbook name from Folder A) Next Help appreciated. Celeste |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each workbook in Folder A Copy Values & Format of eachSheet2...
Thank you for the tip but I was looking beyond that.
In Excel if you want to copy Values & Formats, as far as I now, you have to do it in 2 steps. Can it be done in one sterp in VBA ? Have a good day, Celeste |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each workbook in Folder A Copy Values & Format of each Sheet2...
Hi Celeste,
If you look at other examples, you will see that Ron provides the optional ability to copy values and formats. However, try changing an instruv=ction like: destrange.Value = sourceRange.Value to: sourceRange.Copy With destrange .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False End With or, perhaps: sourceRange.Copy Destination:=destrange. --- Regards. Norman "u473" wrote in message ... Thank you for the tip but I was looking beyond that. In Excel if you want to copy Values & Formats, as far as I now, you have to do it in 2 steps. Can it be done in one sterp in VBA ? Have a good day, Celeste |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each workbook in Folder A Copy Values & Format of eachSheet2...
thank you Norman, this is what I was after.
Celeste. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each workbook in Folder A Copy Values & Format of each Sheet2...
Hi Norman
After I update the page I forget to add this I see now Will add it to the page this evening -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Norman Jones" wrote in message ... Hi Celeste, If you look at other examples, you will see that Ron provides the optional ability to copy values and formats. However, try changing an instruv=ction like: destrange.Value = sourceRange.Value to: sourceRange.Copy With destrange .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False End With or, perhaps: sourceRange.Copy Destination:=destrange. --- Regards. Norman "u473" wrote in message ... Thank you for the tip but I was looking beyond that. In Excel if you want to copy Values & Formats, as far as I now, you have to do it in 2 steps. Can it be done in one sterp in VBA ? Have a good day, Celeste |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
[OT] For Each workbook in Folder A Copy Values & Format of each Sheet2...
Hi Ron,
After I update the page I forget to add this I see now Will add it to the page this evening Excellent! [OT] BTW: Vis-a-vis the thead "Open files with a variable name in a folder get name" are you aware of any reason for JW's GetValue function not to work on a network file? --- Regards. Norman |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
[OT] For Each workbook in Folder A Copy Values & Format of each Sheet2...
"Open files with a variable name in a folder get name"
I will read the thread this evening Norman -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Norman Jones" wrote in message ... Hi Ron, After I update the page I forget to add this I see now Will add it to the page this evening Excellent! [OT] BTW: Vis-a-vis the thead "Open files with a variable name in a folder get name" are you aware of any reason for JW's GetValue function not to work on a network file? --- Regards. Norman |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each workbook in Folder A Copy Values & Format of each Sheet2...
Add it to the Tip section
http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi Norman After I update the page I forget to add this I see now Will add it to the page this evening -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Norman Jones" wrote in message ... Hi Celeste, If you look at other examples, you will see that Ron provides the optional ability to copy values and formats. However, try changing an instruv=ction like: destrange.Value = sourceRange.Value to: sourceRange.Copy With destrange .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False End With or, perhaps: sourceRange.Copy Destination:=destrange. --- Regards. Norman "u473" wrote in message ... Thank you for the tip but I was looking beyond that. In Excel if you want to copy Values & Formats, as far as I now, you have to do it in 2 steps. Can it be done in one sterp in VBA ? Have a good day, Celeste |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each workbook in Folder A Copy Values & Format of eachSheet2...
Wooowww!!!
Thank you for your help. I am happy to find that my question contributed to extend your "Bible". Have a good day, Celeste |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy data from sheet2 to sheet1 when sheet2 has variable # of rows | Excel Discussion (Misc queries) | |||
Cut & Paste Excel Format & Values to New Folder in PdF | Excel Programming | |||
How to copy a sheet from a workbook to another in different folder | Excel Programming | |||
Copy values from Sheet1 to Sheet2 | Excel Discussion (Misc queries) | |||
Copy values only, sheet1 to sheet2 without Selection | Excel Programming |