Copy data from one workbook to another. then delete the original data

[vonryan]

Hi All,

I am trying to achieve the following in VBA.

I have a master workbook called Master.xlsm.

I have a number of workbooks called Engineer1 to X

Engineer workbook only contains 1 worksheet (Sheet1)

I would like to be able to copy all the contents from Engineer1 workbook for all the Engineer workbooks to the Master Workbook.

I want to say copy rows 2 to 10 from Engineer 1 workbook and then copy rows 2 to 10 from Engineer2 workbook to the the cells below the data from engineer1.

I have tried a few examples from the internet but I cannot get the code to work.

Please help. I have attached my base files for your easy reference.

Vonryan

[#2]

@vonryan 

Your approach is possible but these days I would recommend the use of Power Query.  Combining a set of files from a folder is core functionality of PQ and once further 'Engineer' files are added to the folder all that is needed is to click the refresh button.  Ideally the data should all be contained in Tables

Data ribbon tab

Get Data

From File

From Folder

Choose 'combine'

Select the Table to import from each file

Edit as needed

Close and load to sheet within Master

[#3]

Peter, thanks for your reply. I have tried your described method but I seem to be struggling.

I have one file Master.xlsm on D:\

I have four files Engineer1 thro Engineer4 on D:\Test\

I go to data ribbon and select New Query\From File\ From Folder and set the path to D:\Test

the query selects the 4 files and I press Combine and load.

THe combinne dialogue window appears and I select Example file as Engineer1. The prview pane show me the layout which is fine.

I press the OK button and the query starts running.

I then get a error message 
[Expression.Error] The key didn't match any rows in the table..

Please see attached screenshot

What am I doing wrong please

Vonryan@Peter Bartholomew 

[#4 · Accepted solution]

@vonryan 

The screenshot is very close to that given by working versions.

One thought is that the unit of interaction between PowerQuery and Excel is the Table.  I think it may be possible work with Named Ranges or the UsedRegion of a Sheet but they are not the preferred options. 

Unless you have already done it, I would suggest going through each of your four files and converting the data to a Table [Ctrl/T].  This will give a default name of Table1 but it can be renamed to give some more meaningful reference such as 'EngineeringTask' (the same name in each file).  Then, within the Combine & Edit dialogue, select the Table name to be the basis for extracting data from the sample file.

Fingers crossed this takes us a step forward!

[#5]

@Peter Bartholomew , From Folder connector works fine with entire sheets. The only point data for all files in the folder have to have the same structure.

[#6]

@Sergei Baklan 

Thank you.  From the selections available on the combine files menu, I suspected that was the case.  I wouldn't have personal experience because I haven't used location (Sheet and cell notation) to reference data objects for a number of years now.  Tables are simply a more natural starting point for me and, most recently, this is followed by spilt arrays in which only the anchor cell is named.

Oddly enough, it is sometimes difficult to establish a dialogue with Excel users; wonder why that is? ;)

[#7]

@Peter Bartholomew ,

That could be tables. With them even easier. But with that all your files  are to be with exactly the same Table1 with exactly the same column names. Only values could be different. What is done, you take any file in the folder as a sample one, transform it (it could be Tables, Named ranges or entire sheet) and generated for such transformation function is applied to all files in the folder. If files have different structure you failed.

Oh, Excel users are quite different. Even if speak about the Excel, sometimes we speak on different languages. That's not good and not bad, that as it is.

[#8]

Gentlemen,

You have been a great help. Thank you. I have achieved my goal thanks to your help.

I will investigate Tables when I get the chance.

Vonryan

@Sergei Baklan 

[#9]

Hi Peter,

I have a new challenge if you are interested.

I wish to hide culumns in a worksheet based on dates.

On worksheet 1 from B2:Q2 are cells with dates. Each cell is one week apart.

I have created a button on the page and it triggers a sub routine that is supposed to check the current date (Today()) function and evaluate this date into a week number using [Weeknum(Today()].

It then checks the cell Range (B2:Q2) date and evaluates these dates for [Weeknum(c.Value)]

If the week number of the date in the Range of cells B2:Q2 is <> to week number of today then hide the columns as I only want to see this weeks column.

I have written the following code to try and resolve my isssue by using a message box to return the values of the variables.

I cannot get the Week Number function to work.

Sub ShowCurrentWeekOnly()
Dim c As Range, msgValueC As Long, msgValueWeeknumC As Long, msgValueDate As Long, msgValueWeeknumDate As Long, WeekNum As Long
Application.EnableEvents = False

On Error Resume Next

For Each c In Range("B2:Q2")
msgValueC = c.Value
WeekNum = Evaluate("=weeknum(msgValueC)")
msgValueWeeknumC = WeekNum
msgValueDate = Date
msgValueWeeknumDate = [Weeknum(Date()]
MsgBox "Returned values are " & msgValueC & " " & msgValueWeeknumC & " " & msgValueDate & " " & msgValueWeeknumDate
If WeekNum <> [weeknum(Date())] Then
c.EntireColumn.Hidden = True
Else
c.EntireColumn.Hidden = False
End If
Next
On Error GoTo 0
Application.EnableEvents = True

End Sub

The week number always returns zero.

I wonder if you can help.

@vonryan 

[#10]

@vonryan 

The statement

WeekNum = [Weeknum(msgValueC)]

gave me an error 2029.

You could calculate the week numbers on the spreadsheet and then read them from the module using something of the form

WeekNumber = c.Offset(1).Value

Alternatively

WeekNum = Application.WorksheetFunction.WeekNum(msgValueC)

seems to work OK.

BTW, I hate seeing direct references to ranges in VBA.  A user (could be you) inserts a row and the code crumbles.  A named range is far safer.

[#11]

Dear Peter,

Thank you for your help.

Final code looks like this and works fine.

Sub ShowCurrentWeekOnly()
Dim c As Range, WeekNumC As Long, WeekNumNow As Long
Application.EnableEvents = False

On Error Resume Next

For Each c In Range("B2:Q2")
WeekNumC = Application.WorksheetFunction.WeekNum(c.Value)
WeekNumNow = Application.WorksheetFunction.WeekNum(Date)
If (WeekNumC <> WeekNumNow) Then
c.EntireColumn.Hidden = True
Else
c.EntireColumn.Hidden = False
End If
Next
On Error GoTo 0
Application.EnableEvents = True

End Sub

I will also do some research on Named Ranges as I do not know how to do this yet.

Vonryan

@Peter Bartholomew 

[#12]

Dear Peter,

Just an update after my research on Named Ranges.

The code looks like this and everything is good. Thanks for the guidance.

Sub ShowCurrentWeekOnly()
Dim c As Range, WeekNumC As Long, WeekNumNow As Long
Application.EnableEvents = False

On Error Resume Next

For Each c In Range("LABOURRANGE")
WeekNumC = Application.WorksheetFunction.WeekNum(c.Value)
WeekNumNow = Application.WorksheetFunction.WeekNum(Date)
If (WeekNumC <> WeekNumNow) Then
c.EntireColumn.Hidden = True
Else
c.EntireColumn.Hidden = False
End If
Next
On Error GoTo 0
Application.EnableEvents = True

End Sub

@vonryan 

[#13]

@vonryan 

That will do it :)