Dear Steve,

I’m a newbie for this forum. I’m looking for this kind of solution for my excel files and I found this post. Very useful.

However I do need an extra function if is possible. I need that based on a condition in a specific column of each workbooks (common in the files that I have) the macro runs and give me the rows that I pretend.

Example: I do have 12 files with the overtime of the employees during Jan to Dec (Column A – Name, Column B – Date, C – Nº Hours). I want to compile the information by selecting when Column A = John. The new file will give me the resume of John/hours during that year in a single file).

Thanks for your help,
Drsker

Hi Steve
This is nearly what I am wanting. I need the formula to only import select(I have a list on the open workbook) files, not all the saved saved in the location. Can you please help me?

Hi Steve,

Do you have a code that go in specific folder let say C:/Path/List/

1.) On that folder there are multiple List excel files (List 1.xls, List 2.xls etc) having 3 sheets (TabName1, TabName2, TabName3) on each List#.xls

2.) Is there a way to only copy a specific tab name (eg just TabName2) in a Master file? TabName2 is unique per excel file

3.) Can I also have a macro that Master file will populate all the excel in C:/Path/List/, if it is exsiting in the master file it will not generate another copy of the TabName2

Thanks in advance

Hi,

Thanks for your response Steve, I have no backgrounnd in VBA, can i ask you to provide the correct code please. Sorry!

1) change the line of the code with the new path. The path is explicitly written into the code
done with this…
2) Don’t loop through all the worksheets, just copy the desired one
I need to loop all the files from the source folder to make sure that whatever changes, the master will get it.

3) Not sure I understand, but it seems that you want to check the existence of a worksheet into the file and only copy if it doesn’t exist. add that as an IF to the code to check if the worksheet name is already into the target worksheet.
can I ask a sample If code for this. You’re correct I need to copy the document not yet existing or just to overwrite the existing document to the latest one.

Hi Steve!
What about copying a specific cell (say E6) from multiple .csv files to just one worksheet?
I tried for hours and can’t figure it out…

Hi Steve,

I’m trying to apply the above example to paste the data in columns (not in rows) but the values are being pasted all in the same column. So I only can see the result from the last file copied.

I’m using:
———————
Option Explicit
Sub CombineMultipleFiles()
‘ Path – modify as needed but keep trailing backslash
Const sPath = “C:MyPath”

Dim sFile As String
Dim wbkSource As Workbook
Dim wSource As Worksheet
Dim wTarget As Worksheet
Dim lColumns As Long
Dim lMaxSourceColumn As Long
Dim lMaxTargetColumn As Long
‘Dim blnNoHeader As Boolean

On Error GoTo ErrHandler
Application.ScreenUpdating = False

Set wTarget = ActiveSheet
lColumns = wTarget.Columns.Count
sFile = Dir(sPath & “*.xlsx*”)
Do While Not sFile = “”
Set wbkSource = Workbooks.Open(Filename:=sPath & sFile, AddToMRU:=False)
Set wSource = wbkSource.Worksheets(“1”)
lMaxSourceColumn = wSource.Cells(lColumns, 1).End(xlUp).Column
lMaxTargetColumn = wTarget.Cells(lColumns, 1).End(xlUp).Column
wSource.Range(“B5:B8”).Copy _
Destination:=wTarget.Cells(lMaxTargetColumn + 1, 2) ‘to start column B
wbkSource.Close SaveChanges:=False
sFile = Dir
Loop

ExitHandler:
Application.ScreenUpdating = True
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub
———————

What I’m doing wrong?

Many thanks

LL

It’s working fine!! 😀

Many thanks Steve

Have you tried adapting the generic code in this thread? You don’t provide specifics so I can only provide general responses…

Steve

Is this what you are after?
Steve

Code:
Option Explicit
Sub CopyWorksheets()
‘ Path – modify as needed but keep trailing backslash
Const sPath = “C:MyPath”
Dim sFile As String
Dim wbkSource As Workbook
Dim wSource As Worksheet
Dim wbkTarget As Workbook

On Error GoTo ErrHandler
Application.ScreenUpdating = False

Set wbkTarget = ActiveWorkbook
sFile = Dir(sPath & “*.xls*”)
Do While Not sFile = “”
Set wbkSource = Workbooks.Open(Filename:=sPath & sFile, AddToMRU:=False)
For Each wSource In wbkSource.Worksheets
With wbkTarget
wSource.Copy After:=.Sheets(.Sheets.Count)
End With
Next
wbkSource.Close SaveChanges:=False
sFile = Dir
Loop

ExitHandler:
Application.ScreenUpdating = True
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

I have used this code, but it brings all files saved in location. I want it to bring only the files listed in column A from my open workbook.

Hi Steve
I have attached a file, its also has some screen shots. Thanks so much for your help