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]

I can't reproduce the "empty rows" problem. When I run the macro, it imports the data properly starting at row 2 with no blank rows. Try stepping through the macro one line at a time using the F8 key. After each file is opened and the data copied and pasted to the destination sheet, check the destination sheet to see if there are any blank rows. If there are, then the last opened file may be the problem.

 

[clutcher]

I can't reproduce the "empty rows" problem. When I run the macro, it imports the data properly starting at row 2 with no blank rows. Try stepping through the macro one line at a time using the F8 key. After each file is opened and the data copied and pasted to the destination sheet, check the destination sheet to see if there are any blank rows. If there are, then the last opened file may be the problem.

Thank you, will do so soon and let you know later.

 

[clutcher]

Mumps, looking to add calendar to my file but experiencing an error. Please can you help take a look at the code? The problem is that whenever I select today's date, it does not work, but if I were to click on days before or after today when the calendar

pops up, it works. It also works if I select another day, then select today's date. I want it to work if I select today's date. Thank you.

 

[mumps]

I can't access the picture you tried to post. It's difficult to help without seeing your file. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do with your calendar. If the workbook contains confidential information, you could replace it with generic data. I don't need all the data, just enough to be representative of how it is organized.

 

[clutcher]

Understood, please checkout the info I sent you.