I have data in one sheet in a workbook. I want to distribute it across multiple sheets in a another book. How to do it, here is the diagram.
Currently I am using the following code but it does not work the way it is suppose too. This is just a starting point for me.
Dim row1, row2
Dim i As Integer
Dim cell1 As String
' this is just an example where I am trying to loop through 3 cells but it does not work
' the cells in my example are in G14,G15 and G16
Dim wbk1 As Workbook, wbk2 As Workbook
strFirstFile = "c:\Book1.xls"
strSecondFile = "c:\Book2.xls"
Set wbk1 = Workbooks.Open(strFirstFile)
Set wbk2 = Workbooks.Open(strSecondFile)
For i = 14 To 16
With wbk1.Sheets("Data")
Cells(i, 7).Copy
End With
With wbk2.Sheets("MyData")
Cells(i, 5).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End With
Next i
The actual mapping in my example is like this
Book1.xls Book2.xls
sheet1->B3 -> Company->A3
sheet1->C3 -> Address->C3
sheet1->E3 -> Popularity->D3
If I can achieve this, my actual project is almost the same.
Best Answer
This solution has been sustantially rewritten in light of the revised question.
This solution assumes the macro SplitSheet is in its own workbook. Its needs two file names which are hard coded as Source.xls and Dest.xls in this version. This versions assumes all three workbooks are or will be in the same folder. The source workbook must exist before the macro is run. The destination workbook must not exist.
The question has four columns but the real problem has sixty. The solution is designed to resize to the dimensions of Sheet1 (also hard coded). Which columns are to be moved, where to and how named are controlled by three arrays which can be enlarged from their current three entries. The code uses the actual size of these arrays.
I hope that every difficult statement is fully explained. Best of luck.