Copying/pasting the selected data from a query table to excel sheet
I have the following macro that copies the data from an excel query table (not pivot table) to an excel sheet. The macro is a primitive one because of my limited knowledge about vba; so it definitely needs revision. I appreciate any help.
The macro have two parts, because the first part does not paste data to the destination unless I add the second part.
I have the following macro that copies the data from an excel query table (not pivot table) to an excel sheet. The macro is a primitive one because of my limited knowledge about vba; so it definitely needs revision. I appreciate any help.
The macro have two parts, because the first part does not paste data to the destination unless I add the second part.
| OnChange1.xlsm | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | |||
| 1 | KLM | ytd | daily% | RYMX | ytd | daily% | DSLX | ytd | daily% | ||||
| 2 | August 18, 2023 | 34511 | -5.18% | 0.00% | 41.36 | 0.63% | 0.00% | 22.94 | -3.78% | 0.00% | |||
| 3 | August 21, 2023 | 34543 | -5.10% | 0.09% | 41.47 | 0.90% | 0.27% | 23.85 | 0.04% | 3.97% | |||
| 4 | August 22, 2023 | 34388 | -5.52% | -0.45% | 41.50 | 0.97% | 0.07% | 23.58 | -1.09% | -1.13% | |||
| 5 | August 23, 2023 | ||||||||||||
| 6 | August 24, 2023 | ||||||||||||
| 7 | August 25, 2023 | ||||||||||||
| 8 | August 26, 2023 | ||||||||||||
| 9 | |||||||||||||
| 10 | |||||||||||||
| 11 | |||||||||||||
destntn | |||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D9:D11,D2:D4 | D2 | =IF(C2=0,"",(C2-36398)/36398) |
| G9:G11,G2:G4 | G2 | =IF(F2=0,"",(F2-41.1)/41.1) |
| J9:J11,J2:J4 | J2 | =IF(I2=0,"",(I2-23.84)/23.84) |
| K9:K11,H9:H11,E9:E11,K3:K4,H3:H4,E3:E4 | E3 | =IF(C3=0,"",(C3-C2)/C2) |
| OnChange1.xlsm | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | Private Sub Worksheet_Change(ByVal Target As Range) | |||||||||
| 2 | Dim src As Worksheet, dest As Worksheet | |||||||||
| 3 | Dim NextRow As Long | |||||||||
| 4 | Set src = Sheets("source") | |||||||||
| 5 | Set dest = Sheets("destntn") | |||||||||
| 6 | 'Application.EnableEvents = False | |||||||||
| 7 | If Target.Address = "$B$2" Then | |||||||||
| 8 | NextRow = dest.Cells(Rows.Count, "C").End(xlUp).Row | |||||||||
| 9 | If dest.Range("C" & NextRow).Value <> src.Range("B2").Value Then _ | |||||||||
| 10 | dest.Range("C" & NextRow + 1).Value = Range("B2").Value | |||||||||
| 11 | End If | |||||||||
| 12 | ||||||||||
| 13 | ||||||||||
| 14 | If Target.Address = "$B$3" Then | |||||||||
| 15 | NextRow = dest.Cells(Rows.Count, "F").End(xlUp).Row | |||||||||
| 16 | If dest.Range("F" & NextRow).Value <> src.Range("B3").Value Then _ | |||||||||
| 17 | dest.Range("F" & NextRow + 1).Value = Range("B3").Value | |||||||||
| 18 | End If | |||||||||
| 19 | ||||||||||
| 20 | ||||||||||
| 21 | If Target.Address = "$B$4" Then | |||||||||
| 22 | NextRow = dest.Cells(Rows.Count, "I").End(xlUp).Row | |||||||||
| 23 | If dest.Range("I" & NextRow).Value <> src.Range("B4").Value Then _ | |||||||||
| 24 | dest.Range("I" & NextRow + 1).Value = Range("B4").Value | |||||||||
| 25 | End If | |||||||||
| 26 | ||||||||||
| 27 | ||||||||||
| 28 | 'SECOND PART | |||||||||
| 29 | ||||||||||
| 30 | If Target.Address = "$B$2" Then | |||||||||
| 31 | NextRow = dest.Cells(Rows.Count, "C").End(xlUp).Row | |||||||||
| 32 | If dest.Range("C" & NextRow).Value <> src.Range("B2").Value Then _ | |||||||||
| 33 | dest.Range("C" & NextRow + 1).Value = Range("B2").Value | |||||||||
| 34 | End If | |||||||||
| 35 | ||||||||||
| 36 | If Target.Address = "$B$3" Then | |||||||||
| 37 | NextRow = dest.Cells(Rows.Count, "F").End(xlUp).Row | |||||||||
| 38 | If dest.Range("F" & NextRow).Value <> src.Range("B3").Value Then _ | |||||||||
| 39 | dest.Range("F" & NextRow + 1).Value = Range("B3").Value | |||||||||
| 40 | End If | |||||||||
| 41 | ||||||||||
| 42 | If Target.Address = "$B$4" Then | |||||||||
| 43 | NextRow = dest.Cells(Rows.Count, "I").End(xlUp).Row | |||||||||
| 44 | If dest.Range("I" & NextRow).Value <> src.Range("B4").Value Then _ | |||||||||
| 45 | dest.Range("I" & NextRow + 1).Value = Range("B4").Value | |||||||||
| 46 | End If | |||||||||
| 47 | ||||||||||
| 48 | Application.EnableEvents = True | |||||||||
| 49 | End Sub | |||||||||
Sheet2 | ||||||||||
