return data to specific columns?
 

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

return data to specific columns?
 

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" wrote in
message ...

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

return data to specific columns?
 

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

return data to specific columns?
 

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.


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