I'm going to be generating some graphs from a lot of data located in multiple workbooks. The data is formatted exactly the same in all workbooks and reside in folders all at the same level. I'm going to be bringing parts (ranges) of the data into one final workbook where I'll generate my graphs from.
This made me think that this sort of thing is ripe for VBA automation. Only problem, I'm a novice. I've tried writing pseudo code and then replacing it with what I think is correct VBA. I've looked around for examples, and tried Excel help files, but I'm missing some important steps somewhere...and some basic steps as well.
Lots of things seem to be wrong (... at least you'll have something to smile about before the weekend). If anyone can point out where my brain has abandoned me, I'd be very grateful.
Also, how do you add the name of the file that the ranges came from in Column B on the same rows? This is something that would really help me but I can't find an example of how to do it.
Sub CopySourceValuesToDestination()
Dim DestPath As String
Dim SourcePath As String
Dim Folder As Variant
Dim Folders As Variant
Dim FileInFolder As Variant
Dim Range1 As Range
Dim Range2 As Range
Dim DesitnationPaste1 As Variant
Dim DesitnationPaste2 As Variant
Folder = Array("ABC", "DEF", "GHI", "JKL")
FileInFolder = Array("ABCFile", "DEFFile", "GHIFile", "JKLFile")
''My final Excel file sits in the parent folder of the source files folders
DestPath = "S:\Common\XYZ\Michael S\Macrotest\"
''Each file has it's own folder, and there are many specific files in each
SourcePath = "S:\Common\XYZ\Michael S\Macrotest\ + Folder"
''Always the same in each of my source files
Range1 = Cells("C4:C8")
Range2 = Cells("C17:D21")
''Below I 'm trying to paste Range1 into Column C directly under the last used cell
DestinationPaste1 = Range("C5000").End(xlUp).Offset(1, 0)
''Below I 'm trying to paste Range2 into Column D directly under the last used cell
DestinationPaste2 = Range("D5000").End(xlUp).Offset(1, 0)
''Trying to make it loop through the folder and the_
''files...but this is just a guess
For Each Folder In Folders
''Again a guess
F = 0
''The rest of the process would open a source file copy_
''Range1 and then opening the Destination file and pasting_
''it in the Row 1 of Column C. Hopefully it then goes back_
''to the open source file copies Range2 and pastes it the_
''next Row down in Column C
Workbooks.Open FileName:=SourcePath + FileName + "Source.xls"
Workbook.Sheet(Sheet2).Range1.Copy
Workbook.Open FileName:=DestPath + "Destination.xls"
Workbook.Sheet(Sheet1).DestinationPaste.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
Operation:= xlNone, SkipBlanks:=False, Transpose:=True
Windows(SourcePath + FileName + "Source.xls").Activate
Workbook.Sheet(Sheet2).Range2.Copy
Workbook.Open FileName:=DestPath + "Destination.xls"
Workbook.Sheet(Sheet1).DestinationPaste.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True
Windows(SourcePath + FileName + "Source.xls").Activate
ActiveWorkbook.Close
F = F + 1
Next
End Sub
The outcome of the process would look like the image below - but without the colours or the additonal "_b":
Many thanks again for any help you can give me.
Michael.