Folder Path: C:\Test Excel file na 1

[jasonhuibers]

Folder Path: C:\Test
Excel file name: test.xlsx
Columns A2:A22 and D222

Want to take the data from those columns and put it into a new xlsx

 

[SkipVought]

Hi,

One time?

Or on a periodic basis?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[jasonhuibers]

One time... there maybe more than one file but files are named with a datetimestamp at end

 

[SkipVought]

Do you want them in the same rows and columns?

I assume that there’s data in columns B & C that you don’t want.

Why would you want to code a simple one time event?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[jasonhuibers]

In new Workbook named Sample.xlsx, I want to run the macro that will take the data from all the workbooks in the folder c:\Test from Columns A2:A22 and D222 and paste it into the Sample.xlsx.

 

[SkipVought]

Well that’s a different question. Its not just doing a copy n paste from one workbook to another, which I would certainly not write a procedure for. Now your question makes sense.

Is there anything else we need to know before we invest some time in a targeted solution?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Dim oFSO As Object, oFolder As Object, oFile As Object
Dim wsSample As WorkSheet, lRow As Long

Set wsSample = Workbooks.Open("c:\test\sample.xlsx").Sheets(1)

Set oFSO = CreateObject("Scripting.FileSystemObject")

For Each oFile in oFSO.GetFolder("c:\Test").Files
    With oFile
        Select Case .Name
           Case "sample.xlsx"
           Case Else
               With Workbooks.Open(oFile.Path)
                   lRow = wsSample.UsedRange.Rows.Count + 1

                   .Sheets(1).Range("A2:A22").Copy wsSample.Cells(lRow, 1)
                   .Sheets(1).Range("D2:D22").Copy wsSample.Cells(lRow, 4)

                   .Close
               End With
        End Select
    End With
   
    wsSample.Save
Next

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[jasonhuibers]

Hey Skip... I receive an error: Compile Error Method or data member not found - on line wsSample.Save

 

[SkipVought]

Sorry, thought I fixed.

wsSample.Parent.Save

BTW my procedure assumes that all data in every workbook is on the very first sheet.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[jasonhuibers]

Thank you for all the help.