How to extract data from one sheet to another - Using either a \'Macro\' or a \'

Question

How to extract data from one sheet to another - Using either a 'Macro' or a 'Formula'

I need your help in fixing a problem that I have with excel.

I have attached a spreadsheet that has two tabs (handover and issues) with the data in it. I need to move some data to be moved from some cells of the 'Issues' tab to some cells of the 'handover' tab.

On the basis of 'Col O' of tab 'issues', I need the data to be extracted.

1. for E.g., if the "COl O" has the value "Shift A", then --

a) the "Col E" data should be moved to "B16" of "handover" tab,

b) the "Col H" data should be moved to "C16" of "handover" tab,

c) the "Col Q" data should be moved to "D16" of "handover" tab

d) the "Col R" data should be moved to "C27" of "handover" tab

2. for E.g., if the "COl O" has the value "Shift B", then --

a) the "Col E" data should be moved to "B48" of "handover" tab,

b) the "Col H" data should be moved to "C48" of "handover" tab,

c) the "Col Q" data should be moved to "D48" of "handover" tab

d) the "Col R" data should be moved to "C61" of "handover" tab

3. for E.g., if the "COl O" has the value "Shift C", then --

a) the "Col E" data should be moved to "B81" of "handover" tab,

b) the "Col H" data should be moved to "C81" of "handover" tab,

c) the "Col Q" data should be moved to "D81" of "handover" tab

d) the "Col R" data should be moved to "C94" of "handover" tab

Now, while we are moving "Col H", we have two ways to move the data. (a) either copy the entire data the way it is entered, or (b) pick one of the header inside the cell and move it only (e.g., rather than copying all the fields, just move "Description" field to "handover' tab).

I would appreciate if someone could provide me with the options to make that automation possible.

Explanation / Answer

This is worksheet event code - to put it into your workbook, select the [Issues] sheet and right-click on it's name tab and choose {View Code} from the list of options that appears.

Copy the code below and paste it into the code module presented to you below any existing entries in the code module presented. If there is already a _Change() event in that code, we'll have to deal with that later also - there can only be one of those per worksheet, but the one can do several different things.

Close the VB Editor by using its red-x or pressing [Alt]+[Q].

Save the workbook as a macro enabled (type .xlsm or .xlsb) workbook.

The code only does its work when you have just changed the data in a single cell in columns E, H, O, Q or R and there is a proper entry in column O of the row. So here's the "proof of concept" code:

Private Sub Worksheet_Change(ByVal Target As Range)
'you can revise these Const values to alter the way
'the code works - handy if your worksheet layout changes
'so we can test what column a change took place in
'source [Issues] sheet layout
Const colE = 5
Const colH = 8
Const colO = 15
Const colQ = 17
Const colR = 18
'make these all UPPERCASE for proper testing results
Const shiftA = "SHIFT A"
Const shiftB = "SHIFT B"
Const shiftC = "SHIFT C"
'destination [Handover] sheet definitions
Const destSheetName = "Handover"
'these map the columns to copy data into
'rows will be determined later
Const fromCol_E = "B"
Const fromCol_H = "C"
Const fromCol_Q = "D"
Const fromCol_R = "C"
'
'working variables
Dim destWS As Worksheet
Dim currentShift As String
Dim currentRow As Long
Dim destRow1 As Integer
Dim destRow2 As Integer
'only works with single cell changes on the Issues sheet
If Target.Cells.Count > 1 Then
    Exit Sub
End If
Select Case Target.Column
    Case Is = colE, colH, colO, colQ, colR
      currentRow = Target.Row
      currentShift = UCase(Trim(ActiveSheet.Cells(currentRow, colO)))
    Case Else
      'not interested in any other columns
      Exit Sub
End Select
'if no shift entered/selected yet, cannot do anything
If currentShift <> shiftA _
   And currentShift <> shiftB _
   And currentShift <> shiftC Then
    Exit Sub
End If
Set destWS = ThisWorkbook.Worksheets(destSheetName)
Select Case currentShift
    Case Is = shiftA
      destRow1 = 16
      destRow2 = 27

    Case Is = shiftB
      destRow1 = 48
      destRow2 = 61
   
    Case Is = shiftC
      destRow1 = 81
      destRow2 = 94
   
    Case Else
      'should never get here, but just in case
      GoTo CleanupAndExit
End Select
'do the actual copying
With destWS
    .Cells(destRow1, fromCol_E) = Cells(currentRow, colE)
    'we will have to figure out how to deal with
    'breaking up col H info later
    .Cells(destRow1, fromCol_H) = Cells(currentRow, colH)
    .Cells(destRow1, fromCol_Q) = Cells(currentRow, colQ)
    .Cells(destRow2, fromCol_R) = Cells(currentRow, colR)
End With
CleanupAndExit:
Set destWS = Nothing
End Sub

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.