Code Edit to add Browse File Option

[levanoj]

The code below opens all files individually within a specific folder, Copies all then Pastes back the Values only. What I'm looking to change in this code is to instead prompt me to select a folder from my C: Drive and not limit me to just one specific folder then continue with the rest of the process. Can anyone Help?

Sub PasteSpclOnly()
Application.DisplayAlerts = False
Dim myDir As String, fn As String, ws As Worksheet
myDir = "C:\Documents and Settings\levanoj\Desktop\LAB\"
fn = Dir(myDir & "\*.xls")
Do While fn <> ""
    With Workbooks.Open(myDir & "\" & fn)
        For Each ws In .Worksheets
            ws.UsedRange.Copy
            ws.UsedRange.PasteSpecial xlPasteValues
        Next
        .Close True
    End With
    fn = Dir
Loop
Application.DisplayAlerts = True
End Sub

 

[John_w]

Use http://www.vbaexpress.com/kb/getarticle.php?kb_id=284

 

[levanoj]

Thanks John W but I'm pretty much an amateur when it comes to rewriting code. I basically pasted the code from the link you gave me within my macro and got an error. Can you get more specific as to how exactly I'm suppose to incorporate this macro with the link you provided?

 

[John_w]

It tells you how to use and test it. In short, put the code in a standard module (Insert - Module in the VB Editor).

To use it with your code instead of your hardcoded folder, something like:

Dim fn As String, ws As Worksheet
Dim myDir As Variant
myDir = BrowseForFolder("C:\")

You might need to remove the \ from fn = Dir(myDir & "\*.xls") and the .Open depending on whether or not the folder returned by BrowseForFolder ends with \ - I haven't tested it.