For Each workbook in Folder A Copy Values & Format of each Sheet2...

[u473]

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

 

[Norman Jones]

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

 

[u473]

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

 

[Norman Jones]

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.

 

[u473]

thank you Norman, this is what I was after.
Celeste.

 

[Ron de Bruin]

Hi Norman

After I update the page I forget to add this I see now
Will add it to the page this evening

 

[Norman Jones]

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?

 

[Ron de Bruin]

"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

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?

 

[Ron de Bruin]

Add it to the Tip section
http://www.rondebruin.nl/copy3.htm

 

[u473]

Wooowww!!!
Thank you for your help.
I am happy to find that my question contributed to extend your
"Bible".
Have a good day,
Celeste