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

clutcher

New Member
Joined
Oct 13, 2018
Messages
19
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

Well-known Member
Joined
Apr 11, 2012
Messages
8,506
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

New Member
Joined
Oct 13, 2018
Messages
19
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

New Member
Joined
Oct 13, 2018
Messages
19
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

Well-known Member
Joined
Apr 11, 2012
Messages
8,506
"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?
 
Last edited:

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,506
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?
 
Last edited:

clutcher

New Member
Joined
Oct 13, 2018
Messages
19
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

Well-known Member
Joined
Apr 11, 2012
Messages
8,506
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

New Member
Joined
Oct 13, 2018
Messages
19
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

Well-known Member
Joined
Apr 11, 2012
Messages
8,506
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?
 

Forum statistics

Threads
1,081,578
Messages
5,359,736
Members
400,545
Latest member
Damntheman30

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top