Solved workbook to another workbook

December 26, 2012 at 19:18:29
Specs: Microsoft Windows XP Professional, 2 GHz / 958 MB
hi..

Please help. I have a project which needs to transfer data from 7 tracking files into 1 consolidated file. Each workbook has the same format as well as the consolidated file.

Any way would be greatly appreaciated.


thanks :)


See More: workbook to another workbook

Report •


✔ Best Answer
December 27, 2012 at 22:43:59
Hi

that's pretty good. I've also been working on a solution. You should be able to pick stuff out of mine that will get you what you want.

Be sure to test on copies of all files.

Sub ConsTracking()

FirstCol = 3
LastCol = 70  'BR
FirstRow = 12

Dirloc = "C:\users\MG\Tracking Files\"
File1 = "Tracking File 1.xlsx"
File2 = "Tracking File 2.xlsx"
File3 = "Tracking File 3.xlsx"
File4 = "Tracking File 4.xlsx"
File5 = "Tracking File 5.xlsx"
File6 = "Tracking File 6.xlsx"
File7 = "Tracking File 7.xlsx"



'Make a backup of the Consolidated sheet

NewName = "Consolidated " & Application.Text(Date, "YYYYMMDD") & "-" & Application.Text(Time, "HHMM")

    Sheets("Consolidated").Copy After:=Sheets("Consolidated")
    Sheets("Consolidated (2)").Select
    Sheets("Consolidated (2)").Name = NewName
    
'Clears the data in the Consolidated spreadsheet
    Sheets("Consolidated").Activate
    LastRow = Cells(Rows.Count, FirstCol).End(xlUp).Row
    Range(Cells(2, 1), Cells(LastRow, LastCol)).ClearContents

'Opens 1st file
Workbooks.Open Filename:=Dirloc & File1
Sheets("Data").Activate

'Copies the dats
LastRow = Cells(Rows.Count, FirstCol).End(xlUp).Row
Range(Cells(FirstRow, FirstCol), Cells(LastRow, LastCol)).Copy
'Pastes to Consolidated
Workbooks(1).Activate
Range("c12").PasteSpecial (xlPasteValues)

'File 2
Workbooks.Open Filename:=Dirloc & File2
Sheets("Data").Activate

LastRow = Cells(Rows.Count, FirstCol).End(xlUp).Row
Range(Cells(FirstRow, FirstCol), Cells(LastRow, LastCol)).Copy
Workbooks(1).Activate
Range("c12").End(xlDown).Offset(1, 0).PasteSpecial (xlPasteValues)

'File 3
Workbooks.Open Filename:=Dirloc & File3
Sheets("Data").Activate

LastRow = Cells(Rows.Count, FirstCol).End(xlUp).Row
Range(Cells(FirstRow, FirstCol), Cells(LastRow, LastCol)).Copy
Workbooks(1).Activate
Range("c12").End(xlDown).Offset(1, 0).PasteSpecial (xlPasteValues)

'File 4
Workbooks.Open Filename:=Dirloc & File4
Sheets("Data").Activate

LastRow = Cells(Rows.Count, FirstCol).End(xlUp).Row
Range(Cells(FirstRow, FirstCol), Cells(LastRow, LastCol)).Copy
Workbooks(1).Activate
Range("c12").End(xlDown).Offset(1, 0).PasteSpecial (xlPasteValues)

'File 4
Workbooks.Open Filename:=Dirloc & File5
Sheets("Data").Activate

LastRow = Cells(Rows.Count, FirstCol).End(xlUp).Row
Range(Cells(FirstRow, FirstCol), Cells(LastRow, LastCol)).Copy
Workbooks(1).Activate
Range("c12").End(xlDown).Offset(1, 0).PasteSpecial (xlPasteValues)

'File 6
Workbooks.Open Filename:=Dirloc & File6
Sheets("Data").Activate

LastRow = Cells(Rows.Count, FirstCol).End(xlUp).Row
Range(Cells(FirstRow, FirstCol), Cells(LastRow, LastCol)).Copy
Workbooks(1).Activate
Range("c12").End(xlDown).Offset(1, 0).PasteSpecial (xlPasteValues)

'File 7
Workbooks.Open Filename:=Dirloc & File7
Sheets("Data").Activate

LastRow = Cells(Rows.Count, FirstCol).End(xlUp).Row
Range(Cells(FirstRow, FirstCol), Cells(LastRow, LastCol)).Copy
Workbooks(1).Activate
Range("c12").End(xlDown).Offset(1, 0).PasteSpecial (xlPasteValues)


'Closes all workbooks except consolidated
NoWBS = Workbooks.Count

For i = 2 To NoWBS
   Workbooks(2).Close
Next

End Sub



#1
December 26, 2012 at 21:41:10
Hi
A little more information would be helpful e.g.

How may spreadsheets from each of the 7 tracking WBs need to be consolidated
How often
What is the layout of the sheets
Are all workbooks in the same location
Are they all controlled by one person or many

And anything else you believe might be relevant


Report •

#2
December 27, 2012 at 00:01:34
hi..
Sorry if I do not put any details on it.


- Only one sheet will be transferred to the consolidated workbook and it is the "DATA" Sheet

- All "DATA" sheet for the 7 tracking files looks the same. They are on the same format and all data from the DATA sheet will be transferred to the consolidated workbook sheet named "Consolidated" sheet

- The transfer would be copying from a range say B12:BR (up to last non-blank row).

- The tracking files contains data from previous weeks up to the current data. The consolidation happens on a weekly basis and the 7 tracking files are updated on a weekly basis too which the new data will be added. So when the consolidator do the update only the new data should be copied/transferred to the consolidated file.

- All workbooks will be stored on a same location. Let's say on C:\My Documents\Tracking Files.
The tracking files are actually stored on a mailbox but it can be saved first to a local drive for the transfer.

- It will be maintained by one consolidator but in case the consolidator is not around another person will take charge. 1 to 3 persons must know how it works.


That would actually are the significant details for this scenario.

thank you so much! :)


Report •

#3
December 27, 2012 at 00:28:28
Hi,

Hope this information will help.


- I have 7 tracking files named after the Business Unit
- The workbooks has 4 sheets and one sheet is named as Data and the other 3 sheets are all pivot tables.
- The Data sheet is where the data will be inputted by the user on a weekly basis.
- The Data sheet also contains data from previous weeks and the new data for the current week will only be added at the bottom.
- The Data sheet is a password protected sheet and only cells that needs an input will be unprotected all others should be protected as they have formulas.
- The Data sheet has the same format for all the tracking files including the consolidated file.
- Range from B12:BR will need to be transferred to the consolidated workbook.

- The data from the Data sheet of the 7 tracking files should be transferred to one consolidated workbook (Consolidated Tracking File) sheet named "Consolidated".
- Ranging from B12 to BR (up to the last non-blank row)
- When the consolidator transfer the data, only the newly added data should be transferred.


- All other sheets from the consolidated workbook contains report generated from the consolidated data.
- The 7 tracking files and the consolidated file will be stored on a same location. The name of the file must the same and every changes made will be saved on the same file.


Thank you.


Report •

Related Solutions

#4
December 27, 2012 at 11:09:55
Hi shieldbreakers

Thanks - that was a good explanation of the process and structure. A few more questions ....

How can you tell what is new input in the Tracking sheets - i.e. does each row have a date or a Week# or something that Identifies it

Is there anything in the tracking sheet that identifies the the department it came from (other than the name of the sheet or the file)?

Have you done this manually?

A listing of the col headers might be helpful


Report •

#5
December 27, 2012 at 17:09:17
Hi Altek,

There are no identifier/variable in the file. One way that I'm thinking is that to have a blank consolidated file so that I could transfer alll the data from the tracking files and just save it with a date on it.

A column named Material Group can identify from which file it came from.

Yes I am doing this manually each week and it took me time to consolidate.

What I'm doing is that I will clear the contents of the sheet in the consolidated file and will copy the data from the tracking files and paste it as values on the consolidated file and I'm doing this every week. After consolidation, I will make some reports out of the consolidated data.


Thank you.


Report •

#6
December 27, 2012 at 20:50:45
Hi,

In relation to my query..I've come up with a code and it is now working. But my problem is when I run it again to get the data for another file it only overwrite my data on the consolidated file instead of pasting it on an empty cell after the last cell.

Hope you could help modify this code.

Sub test()
   
   Dim vFile As Variant
   Dim wbTarget As Workbook     'workbook where the data is to be pasted
   Dim wbThis As Workbook       'workbook from where the data is to copied
   Dim strName As String        'name of the source sheet/ target workbook


    'Open the target workbook
    vFile = Application.GetOpenFilename("Excel-files,*.xls", _
        1, "Select One File To Open", , False)
    'if the user didn't select a file, exit sub
    If TypeName(vFile) = "Boolean" Then Exit Sub
    Workbooks.Open vFile

Set wbThis = ActiveWorkbook
    strName = ActiveSheet.Name

wbThis.Activate
Range("C12").Select

lastRw = Range("C" & Rows.Count).End(xlUp).Row
Set myRange = Range("C12:BR" & lastRw)
myRange.Copy

Set wbTarget = Workbooks("Consolidated - Planning file.xls")
wbTarget.Activate
Sheets("DATA").Range("C12").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False



End Sub


Thank you


Report •

#7
December 27, 2012 at 22:43:59
✔ Best Answer
Hi

that's pretty good. I've also been working on a solution. You should be able to pick stuff out of mine that will get you what you want.

Be sure to test on copies of all files.

Sub ConsTracking()

FirstCol = 3
LastCol = 70  'BR
FirstRow = 12

Dirloc = "C:\users\MG\Tracking Files\"
File1 = "Tracking File 1.xlsx"
File2 = "Tracking File 2.xlsx"
File3 = "Tracking File 3.xlsx"
File4 = "Tracking File 4.xlsx"
File5 = "Tracking File 5.xlsx"
File6 = "Tracking File 6.xlsx"
File7 = "Tracking File 7.xlsx"



'Make a backup of the Consolidated sheet

NewName = "Consolidated " & Application.Text(Date, "YYYYMMDD") & "-" & Application.Text(Time, "HHMM")

    Sheets("Consolidated").Copy After:=Sheets("Consolidated")
    Sheets("Consolidated (2)").Select
    Sheets("Consolidated (2)").Name = NewName
    
'Clears the data in the Consolidated spreadsheet
    Sheets("Consolidated").Activate
    LastRow = Cells(Rows.Count, FirstCol).End(xlUp).Row
    Range(Cells(2, 1), Cells(LastRow, LastCol)).ClearContents

'Opens 1st file
Workbooks.Open Filename:=Dirloc & File1
Sheets("Data").Activate

'Copies the dats
LastRow = Cells(Rows.Count, FirstCol).End(xlUp).Row
Range(Cells(FirstRow, FirstCol), Cells(LastRow, LastCol)).Copy
'Pastes to Consolidated
Workbooks(1).Activate
Range("c12").PasteSpecial (xlPasteValues)

'File 2
Workbooks.Open Filename:=Dirloc & File2
Sheets("Data").Activate

LastRow = Cells(Rows.Count, FirstCol).End(xlUp).Row
Range(Cells(FirstRow, FirstCol), Cells(LastRow, LastCol)).Copy
Workbooks(1).Activate
Range("c12").End(xlDown).Offset(1, 0).PasteSpecial (xlPasteValues)

'File 3
Workbooks.Open Filename:=Dirloc & File3
Sheets("Data").Activate

LastRow = Cells(Rows.Count, FirstCol).End(xlUp).Row
Range(Cells(FirstRow, FirstCol), Cells(LastRow, LastCol)).Copy
Workbooks(1).Activate
Range("c12").End(xlDown).Offset(1, 0).PasteSpecial (xlPasteValues)

'File 4
Workbooks.Open Filename:=Dirloc & File4
Sheets("Data").Activate

LastRow = Cells(Rows.Count, FirstCol).End(xlUp).Row
Range(Cells(FirstRow, FirstCol), Cells(LastRow, LastCol)).Copy
Workbooks(1).Activate
Range("c12").End(xlDown).Offset(1, 0).PasteSpecial (xlPasteValues)

'File 4
Workbooks.Open Filename:=Dirloc & File5
Sheets("Data").Activate

LastRow = Cells(Rows.Count, FirstCol).End(xlUp).Row
Range(Cells(FirstRow, FirstCol), Cells(LastRow, LastCol)).Copy
Workbooks(1).Activate
Range("c12").End(xlDown).Offset(1, 0).PasteSpecial (xlPasteValues)

'File 6
Workbooks.Open Filename:=Dirloc & File6
Sheets("Data").Activate

LastRow = Cells(Rows.Count, FirstCol).End(xlUp).Row
Range(Cells(FirstRow, FirstCol), Cells(LastRow, LastCol)).Copy
Workbooks(1).Activate
Range("c12").End(xlDown).Offset(1, 0).PasteSpecial (xlPasteValues)

'File 7
Workbooks.Open Filename:=Dirloc & File7
Sheets("Data").Activate

LastRow = Cells(Rows.Count, FirstCol).End(xlUp).Row
Range(Cells(FirstRow, FirstCol), Cells(LastRow, LastCol)).Copy
Workbooks(1).Activate
Range("c12").End(xlDown).Offset(1, 0).PasteSpecial (xlPasteValues)


'Closes all workbooks except consolidated
NoWBS = Workbooks.Count

For i = 2 To NoWBS
   Workbooks(2).Close
Next

End Sub


Report •

#8
January 1, 2013 at 16:52:11
Thank you Altek :)

Report •


Ask Question