return data to specific columns?

[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!

[Tom Ogilvy]

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

--
Regards,
Tom Ogilvy

"marlea" <[email protected]> wrote in
message news:[email protected]...
>
> 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!
>
>
> --
> marlea
> ------------------------------------------------------------------------
> marlea's Profile:
http://www.excelforum.com/member.php...o&userid=26209
> View this thread: http://www.excelforum.com/showthread...hreadid=465944
>

[caroline]

Why don't you try with multiple/nested "if" statements in formulae. This
would be simpler than using a macro.
--
caroline


"marlea" wrote:

>
> 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!
>
>
> --
> marlea
> ------------------------------------------------------------------------
> marlea's Profile: http://www.excelforum.com/member.php...o&userid=26209
> View this thread: http://www.excelforum.com/showthread...hreadid=465944
>
>

[marlea]

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.


[QUOTE=Tom Ogilvy]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

--
Regards,
Tom Ogilvy