Computer Magazine Online

1 Comment for this entry

• garbo7441
  June 27th, 2010 on 12:13 am

  Here is one way to do it with a VBA event handler and a macro.

  The following example assumes:

  The ‘column going down’ is column A.
  The workbook containing the cells to be ‘copied from’ is named ‘Book1′.
  The workbook to ‘copy to’ is named ‘Book2′.
  The cell in Book2 containing the combined data is cell A1.

  To modify the Event Handler below to a different ‘column going down’, change Line 6 to the column number of your column. To change to column C, the Line 6 would be:

  If ActiveCell.Column = 3 Then

  Line 18 would be:

  If ActiveCell.Column <> 3 Then

  (Column Z would be column 26)

  Copy the code below, modified if necessary to the clipboard:

  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim selLength As Long
  On Error Resume Next
  Columns("IV:IV").Hidden = True
  Application.ScreenUpdating = False
  If ActiveCell.Column = 1 Then
  selLength = Selection.Count
  If selLength = 1 Then
  Range("IV1").Value = Range("IV1").Value & _
  ActiveCell.Value & ", "
  Else
  For Each Cell In Selection
  Range("IV1").Value = Range("IV1").Value & _
  Cell.Value & ", "
  Next
  End If
  End If
  If ActiveCell.Column <> 1 Then
  Range("IV2").Value = Left(Range("IV1"), _
  Len(Range("IV1")) – 2)
  Range("IV1").Value = ""
  End If
  End Sub

  Open your workbook containing the data to be copied.

  Select the worksheet containing the data to be copied, and right click the sheet tab.

  Select ‘View Code’.

  Paste the code into the sheet module editing area to the right.

  In the menus at the top of the VBE, select INSERT > MODULE

  In the macro below, make the following modifications:

  Line 6: Change "Book2" to the name of your workbook to copy to, i.e. "New Data"
  Line 8: Change "A1" to the cell reference you wish to copy the combined data to, i.e. "F5"
  Line 10: Change "A:A" to the column letter of the cell to copy the data to, i.e. "F:F"
  Line 13: Change "Book1" to the name of your workbook containing the data to copy, i.e. "Data"

  Then, copy the modified macro to the clipboard and paste it into the newly created Module1 in the VBE.

  Sub Combine_and_Copy()
  Application.ScreenUpdating = False
  Range("IV2").Select
  Selection.Copy
  ActiveWindow.WindowState = xlMinimized
  Windows("Book2").Activate
  ActiveWindow.WindowState = xlMaximized
  Range("A1").Select
  ActiveSheet.Paste
  Columns("A:A").AutoFit
  Application.CutCopyMode = False
  ActiveWindow.WindowState = xlMinimized
  Windows("Book1").Activate
  ActiveWindow.WindowState = xlMaximized
  ActiveWindow.ScrollColumn = 1
  End Sub

  Close the VBE and return to the worksheet.

  Press ALT + F8

  When the Macros window opens, highlight this macro and click ‘Options..’

  Enter a letter to be used as a keyboard shortcut and click ‘OK’.

  Close the Macros window.

  Save the workbook.

  Minimize this workbook and open the workbook to ‘copy to’ and minimize it.
  Maximize the ‘copy from’ workbook.

  Select a range of cells in your column to ‘copy from’ and press CTRL + your shortcut letter. That range of cells will be ‘comma concatenated’ in the appropriate cell in the ‘copy to’ workbook.

  You can select a single cell.
  You can click any number of random cells in the column.
  You can select a ‘contiguous range of cells’
  You can select a ‘contiguous range of cells’ and any number of random cells.

  When you activate the keyboard shortcut, all will be copied to the other workbook.

  Now, if you want to automatically open the ‘copy to’ workbook when you open the ‘copy from’ workbook, you can add this routine.

  With the ‘copy from’ workbook open, press ALT + F11

  Double click ‘THIS WORKBOOK’ in the Microsoft Excel Objects in the upper left quadrant.

  Paste the following code into the workbook module editing area:

  Private Sub Workbook_Open()
  Workbooks.Open "D:\Book2.xls"
  ActiveWindow.WindowState = xlMinimized
  Windows("Book1").Activate
  ActiveWindow.WindowState = xlMaximized
  End Sub

  Note:

  Change "Book1" to the name of the ‘copy from’ workbook, i.e. "Data".
  Change \Book2.xls" to the name of the ‘copy to’ workbook, i.e. \New Data.xls"
  Modify the path to the exact location of the ‘copy to’ workbook.

  Close the VBE and return to the worksheet.

  Save the workbook.