Looping through a Copy & Paste

**JPSIMMON** · 01-28-2016, 01:06 PM

I have a piece of code that opens a separate Workbook, copy and pastes a few columns, saves and closes the separate workbook: (YYZ.xlsm). I have about 30 other Workbooks in a folder I want to do this to. How can I change this to just open a folder and do the copy and paste function to all workbooks in the folder? Just loop through a folder of workbooks: YYZ, YYX, YYA etc... performing the same function.

    
    Workbooks.Open Filename:= _
    "K:\DRIVE\FOLDERPATH\FOLDERPATH\FOLDER\YYZ.xlsm"
    Application.AskToUpdateLinks = False
    Windows("Final.xlsm").Activate
    Columns("J:K").Select
    Selection.Copy
     
    
    Windows("YYZ.xlsm").Activate
    Sheets("Atts").Select
    Range("A1").Select
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    ActiveWindow.Close
    
End Sub

Thanks

**gmr4evr1** · 01-28-2016, 01:35 PM

See if this link helps out at all...or gets you in the right direction..
http://www.ozgrid.com/VBA/2007-files...lternative.htm

**JPSIMMON** · 01-28-2016, 01:39 PM

Thanks 4evr, still having trouble incorporating the loop into my code though.

**JOHN H. DAVIS** · 01-28-2016, 03:32 PM

Does this help?

Sub JPSIMMON()
Dim wbk As Workbook, Source As String, StrFile As String
Source = "K:\DRIVE\FOLDERPATH\FOLDER\"
StrFile = Dir(Source)
   Do While Len(StrFile) > 0
        Workbooks.Open filename:=Source & StrFile
        StrFile = Dir()
    Loop
For Each wbk In Workbooks
    If wbk.Name <> Thisworkbook.Name And wbk.Name <> "PERSONAL.xlsm" Then
        Thisworkbook.Sheets("Sheet1").Columns("J:K").Copy
        wbk.Sheets("Atts").Range("A1").PasteSpecial xlPasteValues
    End If
    wbk.Save
    wbk.Close
Next wbk
End Sub

I'm not sure about what you're trying to achieve. This will open all workbooks in a folder. Copy Columns("J:K") to Sheet("Atts") - Each workbook in the folder needs to have this sheet. Save and Close each workbook with the changes. Anyway it might give you some ideas. Note: All other workbooks need to be closed except for those associated with the macro.

Looping through a Copy & Paste

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Looping through a Copy & Paste

Re: Looping through a Copy & Paste

Re: Looping through a Copy & Paste

Re: Looping through a Copy & Paste

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Copy 2 cells and paste - looping

Folder Copy/Paste Looping

VBA Looping for copy paste after running analysis

[SOLVED] Help with Looping a copy and paste marco

[SOLVED] VBA Looping Copy and Paste

Looping Copy/Paste Macro

Looping and copy/paste specific rows

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions