VBA Code to copy data from (specific sheet) in multiple workbooks and paste rows with content to Master Workbook

[clutcher]

The goal is to: write a VBA Code to copy data from (specific sheet) in multiple workbooks and paste rows with content to Master Workbook. I have spent the last few days working on this, but I was really excited to come across this site.

Current, when the information desired is copied, I noticed that it does not paste it within the table I created (using CTL + T), the copied items are displayed at the bottom of the table (1 to 100). For example, if I instruct it to copy and paste it starting on the 5th row on the table, it was pasting the information on line 101 (which is the end of the table i created).
I used 'erow = sheet3.cells(Rows.Count,3).End(xlUp).End(xlUp).Offset(1,0).Row (i.e. with End(xlUp) twice, but this appears to paste the information within the table correctly but does not copy all the rows and paste all the rows from each file.

Below is the code being used, please share your thoughts with me:


"Dim MyFile as String
Dim erow
Dim x as workbook
dim y as workbook
.
.
.
Set x = workbooks.open(MyFile)
set y = Thisworkbook
x.activate
x.sheets("sheet3").Range(C5:AN5).copy
y.activate
erow = sheet3.cells(Rows.Count,3).End(xlUp).Offset(1,0).Row
Activesheet.Paste Destination:=Worksheets("sheet3").Range(Cells(erow,3),cells(erow, 41))
x.close
MyFile = Dir
Loop
End Sub"


The other questions I have are:
a) Is there way to copy the files without opening those files on my computer screen?
b) Is there way to prevent it copying a row/file more than once?
Would greatly appreciate your input.
I owe a lifetime of gratitude to whoever is willing to assist with this.

Note: The Titles and rows are consistent in each of the workbook and the name of the specific sheet I want to copy from is the same in each workbook ....and the name if the sheet being pasted to in the master workbook is the same as well.
Both the workbooks being copied from and pasted master worksheet pasted to ...are password protected.

 

[mumps]

A few questions for clarification:
What is the full path to the folder containing the source files?
What is the extension of those files (xlsx,xlsm)?
Are the source files the only files in that folder?
What are the names of the source sheets and the destination sheet?
When you say that the workbooks are protected, do you mean that the worksheets are protected?
What is the password and is it the same for all worksheets?
What do you mean by:

Is there way to prevent it copying a row/file more than once?

Please be detailed in your explanation.

 

[clutcher]

Lastly, what I meant by "Is there a way to prevent the vba code from copying a row/file more than once" is:

If some set of rows were copied before to the master file, those rows would not be copied again if I refresh the masterfile again. Goal is to only select newly added information if possible.

 

[clutcher]

What is the full path to the folder containing the source files?

Answer:
Dim MyFile as String
Dim erow
MyFile = "Dir(C:\MyComputer\Documents\Analysis\)
Do While Len(MyFile) >0
if MyFile = "MasterAnalysis.xlsm" Then
Exit Sub
End if
What is the extension of those files (xlsx,xlsm)?

Answer:
The extension of the source files are xlsx and the extension of the destination is xlsm

Are the source files the only files in that folder?

Answer: Yes the only the source files and the destination file are in the folder

What are the names of the source sheets and the destination sheet?

The names of the source files are: Jan-Data.xlsx, Feb-Data.xlsx....Dec-Data.exlsx
The names of the destination file is: MasterAnalysis.xlsm

When you say that the workbooks are protected, do you mean that the worksheets are protected?

Yes, the worksheets are protected

What is the password and is it the same for all worksheets?

The password is "DataMine" and it is the same for all

 

[mumps]

"Is there a way to prevent the vba code from copying a row/file more than once" is:

The only way I can think of to make that possible is if each row being copied contains a unique identifier, for example, an ID number or some other unique value. Is this the case?

 

[mumps]

The names of the source files are: Jan-Data.xlsx, Feb-Data.xlsx....Dec-Data.exlsx
The names of the destination file is: MasterAnalysis.xlsm

These are the names of the workbooks. I need the names of the source and destination worksheets, for example: "Sheet1", "Sheet2"

Do you want to copy Range(C5:AN5) from each sheet?

 

[clutcher]

The sheet numbers are are same in all workbooks I am copying from. The sheet name is "DataAnalysis" which excel also recognizes as "Sheet11". The master sheet has the same name as well.

Yes, I want to copy starting at Range(C5:AN5) from each sheet. It's column C because information copied starts from the 3rd column.

The second to the last column (starting at AM5) contains an identifier for each sheet. I want to automate auto generation of the values here (which I can do without vba).

 

[mumps]

Unfortunately, I would need an identifier for each row. In other words, the identifier for each row 5 in each source sheet would have to be different. I need the unique identifier in order to recognize which rows have already been copied so that they are not copied a second time.

 

[clutcher]

Please proceed without the identifier for now if this might make it very complicated. The Identifier for each folder is as follows:
For Jan-Data file, the identifier starting in row 5 (in column AM5) is Jan-Raw-00001 to .... the Jan-Raw0-00010 (if all rows with content)
For Feb-Data file, the identifier starting in row 5 (in column AM5) is Feb-Raw-00001 .....................

 

[mumps]

Do you want to copy only one row at a time or a range of rows? In other words, do you always want to copy Range(C5:AN5) or could it be Range(C5:AN10)? If not only Range(C5:AN5), then how many rows do you want to copy form each source sheet?