Solved: Copy Paste using macro involving 6 different workbooks

[fwawergurl16]

Hi, im an intern with a media company, but im dealing with the IT side of things. Im asked to combined all the data from different Excel workbooks using macro into ONE workbook, but Im VERY new to VBA. :dunno

This is what I'm thinking that I need to do (after opening all workbooks involved using macro as well- able to do this part d):
1. select all info from non-active
2. copy all selected
3. find last line in active workbook
4. paste copied onto active workbook after the workbook's last line (last line +1)
5. save non-active & close saved workbook (active workbook remains opened)
6. loop process

Are there any VBA experts who would know how to get this done? Esp for Step 1,3,4, & 6? :help

Any help is MUCH appreciated. Thanks!

[YellowLabPro]

Is this a one time thing?
How many workbooks, how many sheets per book?
Are all the books stored in the same directory?
Are all the sheets named something unique?

[fwawergurl16]

Is this a one time thing?
How many workbooks, how many sheets per book?
Are all the books stored in the same directory?
Are all the sheets named something unique?
1. Yes, it's a one time thing (all 6 goes into 1 each time)
2. There're 6 workbooks each time. In each wb, only ONE sheet.
3. Yes, all books stored in same directory
4. Yes, all the sheets have unique names. But each month when the info tables are generated (from a software by Nielsen), it'll be saved in the same name, in the same folder, & will overwrite the old files, which won't be of use for the month after tht already :P
[e.g.: For January, .xls generated is called Magazine Spot for Grand Total, Cinema Spot for Grand Total,..etc. For February, .xls generated is also called Magazine Spot for Grand Total, Cinema Spot for Grand Total,..etc. Same name, but info inside different.]

I hope the answers helped. Thanks loads!!!

This is something that I found very useful from this forum by 'lostin_space' (but i need it to loop & be useful for all 6 wb). This copies info from my Cinema wb, Sheet1_Cinema, to Magazine wb, Sheet1_Mag:

Public Sub OpenCopyPaste()

Dim myFileName As Variant
Dim SourceWkbk As Workbook
Dim CurrentWkbk As Workbook
Dim testWks As Worksheet
Dim DestCell As Range

myFileName = Application.GetOpenFilename("Excel files,*.xls")

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Set CurrentWkbk = ActiveWorkbook
Set SourceWkbk = Workbooks.Open(Filename:=myFileName)

Set testWks = Nothing
On Error Resume Next
Set testWks = SourceWkbk.Worksheets("Sheet1_Cinema")
On Error GoTo 0

If testWks Is Nothing Then
MsgBox "Missing the worksheet!"
Else
With CurrentWkbk.Worksheets("Sheet1_Mag")
Set DestCell = .Cells(.Rows.Count, "a").End(xlUp).Offset(1, 0)
End With
With testWks
'choose one of these
.Range("a1", .Cells.SpecialCells(xlCellTypeLastCell)).Copy
DestCell.PasteSpecial Paste:=xlPasteAll

Application.CutCopyMode = False

End With
End If

SourceWkbk.Close savechanges:=False

End Sub

[YellowLabPro]

Ok-
I will work on this. I am not the going to be the first one who can whip this out, so it will take me a little time. So if you need it right away, like by the end of tomorrow then just post back saying so.... and we will let another board member whip it out for you. There are several members here who can do this w/out any effort at all.

Cheers,

YLP

[fwawergurl16]

Hi YLP,

Thank u SO much for helping!! *touched* I would hope to get everything done latest by next Tuesday as I would need to run some tests & make sure that it's applicable for the company's data that comes through. I'll try to play around the many, many codes that I've found (which may need LOADS of modification) as well.

Thanks once again! God bless.

[YellowLabPro]

No problem,
I have to finish something up today. But I have been thinking about this today already.
For clarification purposes-
1. This is infact NOT a one time operation, but an ongoing one, yes?
2. The sheets as they are updated, are updated in the same workbook, independent of one another?
3. You will not be archiving the worksheets in the Master Workbook, but rather overwriting the worksheets in the Master Workbook from the Independent workbooks?
4. And these Independent workbooks will contain one and only one worksheet, the one in which you wish to copy to the master, in their respective workbook?

If the answers to all these questions are yes, then I have an approach, if any detail is different please inform and I will alter my approach. As I said, the other boardmembers could whip this out in their sleep, I have to think it through more.

Cheers,

Doug

[rory]

If I might chuck in a couple of additional questions:
1. Does the data in each workbook start in A1? (Just makes the code a little easier)
2. Do you want the resulting workbook saved in the same folder?
3. Would you prefer to select which workbooks you want to copy from each time, or just have the code run on all workbooks in a specific folder?
4. Is there any chance you will end up with more than 65536 rows of data in the master book?
Regards,
Rory

[YellowLabPro]

Rory,
If I am not mistaken and the poster answers "Yes", then I think the way to go about this is to delete all the sheets in the Masterbook each time and just loop through all the workbooks in the directory and copy each one to the Masterbook.
Agree?
If she answers no to any then I have to reconsider my approach... thanks for throwing those questions in.

[rory]

Doug,
My understanding was that all the data is supposed to end up in one sheet in the consolidated workbook. If it's just a sheet copy, it's a doddle! :)
Rory

[YellowLabPro]

I think you are right... Just re-read her original post.... oops
Well still should be an easy one. If she wants to copy over the data each time then we will just delete and go...
I think she should consider copying the sheets to the workbook too, this gives her the option to view the sheets individually, depending on how much data.
Either way a Pivot Table might be helpful... I just like having my data on the independent sheets in-case anything goes awry on the Mastersheet.
But I am going to start plugging away on this for her tomorrow. I will problably need some assistance w/ the details.

Cheers Rory...

[fwawergurl16]

No problem,
I have to finish something up today. But I have been thinking about this today already.
For clarification purposes-
1. This is infact NOT a one time operation, but an ongoing one, yes?
2. The sheets as they are updated, are updated in the same workbook, independent of one another?
3. You will not be archiving the worksheets in the Master Workbook, but rather overwriting the worksheets in the Master Workbook from the Independent workbooks?
4. And these Independent workbooks will contain one and only one worksheet, the one in which you wish to copy to the master, in their respective workbook?

If the answers to all these questions are yes, then I have an approach, if any detail is different please inform and I will alter my approach. As I said, the other boardmembers could whip this out in their sleep, I have to think it through more.

Cheers,

Doug

Answer:
1. Yes, it's an ongoing process.
2. No, each time when there's an update, we generate a new workbook cos the Nielsen system can easily pull out updated data each time (and past data could stil be retrieved at anytime, so no need to store all the exported workbooks each time :) )
3. Practically yes. There will always be a Master Workbook and 6 individual Workbooks. So if we're statically using the same Master each time & not creating new Master, then we'll just over-write it.
4. Yes, each Individual Workbook will only have ONE sheet in it (ALWAYS).

I hope the answers helped clarify. Thanks LOADS!

Regards,
Ruth

[fwawergurl16]

If I might chuck in a couple of additional questions:
1. Does the data in each workbook start in A1? (Just makes the code a little easier)
2. Do you want the resulting workbook saved in the same folder?
3. Would you prefer to select which workbooks you want to copy from each time, or just have the code run on all workbooks in a specific folder?
4. Is there any chance you will end up with more than 65536 rows of data in the master book?
Regards,
Rory
Hi Rory,

Thanks for dropping by to help. Really appreciate it. To help clarify things, I'll answer your Qs:
1. Yes, all data in Workbook starts with A1, BUT, as A1 is the 'title' for each column, so I'm gonna pull data to combine beginning from A2.(It happens for ALL 6 workbooks)
2. Yes, the resulting workbook is to be saved in the same folder.
3. Actually, that's an excellent Q. Would prefer it to run on all workbooks in the specific folder, but currently, the codes that I've done up is to open the workbooks that I want to be copied, then only run the macro. If u could help me with the 'run all workbooks in the specific folder', that'll be great!
4. That's a very tough Q. I REALLY hope that there won't be more than 65536 rows of data in the master book (if not, I'm doomed). I'm concerned with each Individual Workbooks, whether they themselves have too much data as well! Just hope that this situation doesn't take place...eeekk..

I hope the answers helped. Thanks once again for helping!

Cheers.

Ruth

[fwawergurl16]

I think you are right... Just re-read her original post.... oops
Well still should be an easy one. If she wants to copy over the data each time then we will just delete and go...
I think she should consider copying the sheets to the workbook too, this gives her the option to view the sheets individually, depending on how much data.
Either way a Pivot Table might be helpful... I just like having my data on the independent sheets in-case anything goes awry on the Mastersheet.
But I am going to start plugging away on this for her tomorrow. I will problably need some assistance w/ the details.

Cheers Rory...
Hi again.

I don't mind getting the Individual Workbooks copied into the Master Workbook as well. As long as the end result is that I have ONE sheet with the combined data from all the 6 Individual Sheets, I would be shouting hurrah! ;)

Thanks!

Ruth

[rory]

Hi Ruth,
Since you said you needed something by today, I will post this as an interim solution for you to test. You can store it in your Personal.xls workbook as it will create a new master workbook each time and save it in whatever location you specify for the source files (so there should not be a master file in that directory already!) I have added a basic check for more than 65536 rows:
Option Explicit
Sub Consolidation()
Dim wbk As Workbook, wbkMaster As Workbook
Dim wksSource As Worksheet, wksDest As Worksheet
Dim strFile As String, strPath As String
Dim rngLastCell As Range
Dim lngRowCount As Long, lngColumnCount As Long, lngTargRow As Long, lngCounter As Long
Dim varData

' Note: to use fixed path, uncomment next line and change to whatever path you want
' strPath = "C:\Test"

' Allows you to pick a folder each time
strPath = GetFolder

If strPath = "" Then
MsgBox "You must choose a path!"
Exit Sub
End If
Application.ScreenUpdating = False
lngCounter = 1
If Right$(strPath, 1) <> Application.PathSeparator Then strPath = strPath & Application.PathSeparator
strFile = Dir(strPath & "*.xls")
Set wbkMaster = Workbooks.Add
Set wksDest = wbkMaster.Worksheets(1)
wksDest.Name = "Data1"
lngTargRow = 2
Do Until strFile = ""
If Not strFile = ThisWorkbook.Name Then
Set wbk = Workbooks.Open(strPath & strFile)
' Assumes only one sheet
Set wksSource = wbk.Worksheets(1)
Set rngLastCell = LastCellInSheet(wksSource)
With wksSource
varData = .Range(.Cells(2, "A"), rngLastCell)
End With
lngRowCount = UBound(varData, 1)
lngColumnCount = UBound(varData, 2)
If lngTargRow + lngRowCount - 1 > 65536 Then
lngCounter = lngCounter + 1
Set wksDest = wbkMaster.Sheets.Add
wksDest.Name = "Data " & lngCounter
lngTargRow = 2
End If
With wksDest
.Range(.Cells(lngTargRow, 1), .Cells(lngTargRow + lngRowCount - 1, _
lngColumnCount)).Value = varData
End With
lngTargRow = lngTargRow + lngRowCount
wbk.Close False
End If
strFile = Dir
Loop
wbkMaster.SaveAs strPath & "Master.xls"
' wbkmaster.close
Application.ScreenUpdating = True
End Sub
Public Function LastCellInSheet(wks As Worksheet) As Range
' Returns the cell at the bottom right corner of the sheet's real used range
Dim lngLastCol As Long, lngLastRow As Long
lngLastCol = 1
lngLastRow = 1
On Error Resume Next
With wks.UsedRange
lngLastCol = .Cells.Find(what:="*", after:=.Cells(1), _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
lngLastRow = .Cells.Find(what:="*", after:=.Cells(1), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End With
Set LastCellInSheet = wks.Cells(lngLastRow, lngLastCol)
End Function
Function GetFolder() As String
Dim dlg As FileDialog
Set dlg = Application.FileDialog(msoFileDialogFolderPicker)
If dlg.Show = -1 Then
GetFolder = dlg.SelectedItems(1)
End If
End Function


HTH
Rory

[fwawergurl16]

Hi Rory! Thank you very much!! It worked!! *yay* :bow: I wouldn't be able to manage it myself...really appreciate the help.

Btw, i've another Q. I wanted to clean the tables that are generated. Currently, what I do is to run the macro & it prompts me to open the file that I want to clean before cleaning it. But because I've 6 files, I'll be prompted 6 times to open specific files for cleaning.

Is there any way that the macro would just auto open all the files that are involved and clean it after that? The other thing is also each file is cleaned differently (meaning for certain files, 5 columns are added, for some, only 3 are added...etc) I've included some of the codes below. Any ideas? :think:

Sub cleanAll()
Call completeTVClean
Call completePressClean
Call completeRadioClean
Call completeMagClean
Call completeCinemaClean
End Sub
Sub completeMagClean()
Call openMagFile
Call cleanMagFile
Call closeFile
End Sub
Sub cleanMagFile()
Call removeMagFrame
Call selectAllMagData
Call deleteEmptyMagRows1
Call insertMagColumns
Call printMagColumnTitle
Call FillMagMonth
End Sub

[rory]

It could be done but I'd need to see all the code and know how to determine which book to do what cleaning to.
Regards,
Rory

[fwawergurl16]

It could be done but I'd need to see all the code and know how to determine which book to do what cleaning to.
Regards,
Rory
Ooo...how can I send it to u? I've all the macro in one file, but do u want me to upload it here, or send to ur email? Cos some of the Clean Up codes are done by my colleague,but the rest are handled by me.

Just drop a note here or send a private msg to me & i'll get back to u tomoro (off work d). Thanks!

[rory]

Probably easiest to zip it and post it here or, if it's very large, you could use one of the free online storage sites.
Regards,
Rory

[fwawergurl16]

Probably easiest to zip it and post it here or, if it's very large, you could use one of the free online storage sites.
Regards,
Rory
Thanks for all the help. I've attached the relevant sheets: Uncleaned Sheets (not all cos files too big), Cleaned Sheets (just to show how it looks like after cleaning), & the allMacro (contains all the macro).

Huge thank you for helping!

~ Ruth

[rory]

I'm just going through and cleaning up some of the code (lots of unnecessary selection of cells etc. in there) and have one quick question:
in order to identify which files need what doing to them, is there a fixed set of file names I can use so that as the code opens all files in the directory it can look at the filename and determine what should be done to it?
Regards,
Rory

[fwawergurl16]

I'm just going through and cleaning up some of the code (lots of unnecessary selection of cells etc. in there) and have one quick question:
in order to identify which files need what doing to them, is there a fixed set of file names I can use so that as the code opens all files in the directory it can look at the filename and determine what should be done to it?
Regards,
Rory
If u noticed, all the files begin with a specific name (eg: Cinema Spot Report..., TV Spot Report....., Magazine Spot Report......) Each time the report is generated, those are the default names. So, it begins specifically, but it may end differently.

My company would also prefer if the report that goes through the macro (cleaning) could be 'Save As' instead of 'Save' & overwrite the original. How can this be done? Thank u SO much for the endless help!

[rory]

So you want to open the uncleaned sheets, clean them and then save as a different name? Do you want them in the same directory? Currently I have the code set up to clean the files as part of the consolidation routine so it would need tweaking to incorporate your suggestion otherwise the consolidation might run on both the original sheets and the cleaned versions!
Regards,
Rory

[rory]

Try

[rory]

Oops. Try this version - it will create a 'cleaned' directory in the directory you specify for the raw files and save the cleaned copies in there.
HTH,
Rory

[fwawergurl16]

So you want to open the uncleaned sheets, clean them and then save as a different name? Do you want them in the same directory? Currently I have the code set up to clean the files as part of the consolidation routine so it would need tweaking to incorporate your suggestion otherwise the consolidation might run on both the original sheets and the cleaned versions!
Regards,
Rory
Yeah, open uncleaned, clean & save as different name.
Best if the cleaned ones all be chucked into a different directory. If not, when we consolidate, it'll be a prob.

I'll try out the codes that u've provided. Thank u very much. I owe u loads. Will get back to u next week.

Regards,
Ruth

[rory]

The code I posted in my last post has tweaked the consolidation routine so that it will:
1. Prompt you for a directory
2. Create a 'cleaned' directory in the chosen directory if there is not already one there.
3. Open each workbook in the selected directory, clean and format it, copy the data to the master Workbook, then save the workbook in the 'cleaned' directory as "workbook name clean yyyy-mm-dd hh-mm.xls", so it adds a date/time stamp to the name.

Have a good weekend.
Rory

[fwawergurl16]

Hi Rory! Thank u so so much! It works perfectly fine. In fact, i'm in awe of the results of the macro!! *faints*

I'm gonna move on to the next phase of this project: creating the pivot table for the Master using macro (as well!! oh no...). Will do my research & see if there's any help that I could get online :P Thanks a million!!

~ Ruth

[rory]

Ruth,
Glad to help - have fun with stage 2... :)
Rory

[fwawergurl16]

Thank loads, Rory!! Now all's fine d. Without ur help, I wouldn't know what to do...thanks!!