return data to specific columns?

  • Thread starter Thread starter marlea
  • Start date Start date
M

marlea

Hi-

Workbook1 contains the source data:

Column A (ID)/column B (authorName)/column C (title)/...
2/John Doe/Gardening
3/Jane Doe/Plants
3/M. Smith/Trees
...
The data needs to be returned to workbook2 and placed in *specific*
columns depending on the ID value. The 'authorName' should always be
returned to column 1; but the location of 'title' varies depending on
the ID:

So, if the ID in book1 is 3, then in book2 place 'authorName' in column
1 and 'title' in column 3; if the ID is 2, return data to columns 1 and
5.

Is there a macro that will do this? Thanks!
 
Sub CopyData()
Dim rng1 as Range, cell as Range
Dim bk1 as Workbook, bk2 as Workbook
Dim sh1 as Worksheet, sh2 as Worksheet
Dim rw as Long, icol as Long
set bk1 = Workbooks("Workbook1.xls")
set bk2 = Workbooks("Workbook2.xls")
set sh1 = bk1.Worksheets(1)
set sh2 = bk2.Worksheets(1)
set rng1 = sh1.Range(sh1.Cells(2,1),sh1.Cells(2,1).End(xldown))
rw = 2
for each cell in rng1
sh2.Cells(rw,1).Value = cell.offset(0,1).Value
Select Case Cell.Value
Case 1
icol = 4
Case 2
icol = 5
Case 3
icol = 3
Case Else
icol = 2
End Select
sh2.Cells(rw,icol).Value = cell.offset(0,2).Value
rw = rw + 1
Next
End Sub
 
Why don't you try with multiple/nested "if" statements in formulae. This
would be simpler than using a macro.
 
Tom-

Thanks so much for the macro! I just did a test run and it seems to
work. I just wish I understood it...thanks, again.
 
Back
Top