TheGrandPooba
New Member
- Joined
- Jul 1, 2022
- Messages
- 12
- Office Version
- 365
- Platform
- Windows
Hi all,
I'm wondering how I can copy over data that has been manually entered that is next to automatically populating information. This somewhat builds off of my ' Lookup Order#, Copy Paste data into history, then delete orders ' While that solution addressed my previous needs, I'm pretty sure I have to create a new post for an issue that builds off that macro.
The VBA macro I'm using is below, which copy/pastes all orders that match the "Completed order?" cell from OImport into OrderHistory, then deletes those orders from the OImport table. However, the Summary tab the macro is initiated in has manually entered data (tan highlight) next to the automatically populated data (no highlight) that needs to transfer over to OrderHistory as well. Currently, the user would have to remember where the manual info is, run the macro to clear the order off of Summary and OrderImport, then cut/paste the info into OrderHistory.
Code:
XL2BB:
While there are XL2BB references to OrderHistory and OrderImport tabs in Lookup Order#, Copy Paste data into history, then delete orders, here is another example
Example: In the XL2BB above, the user wants to clear order 266805 because its item statuses are all completed (pretend they show complete.) They would type in the order# in G3, and the above VBA would transfer everything on OrderImport to OrderHistory, then delete the rows in OrderImport with order# 266805. This would clear the order from Summary, as Summary A8 is a filter to show OrderImport orders sorted by date. But, none of the info from N8:Q10 would clear off of Summary, as its manually entered.
This is where the problem lay, is there any way to set a range equal to Summary columns N:Q for every row with the "Completed order?" number before the With statement
then after the With- where data copies to OrderHistory and clears OrderImport- cut and paste the range into OrderHistory(the yellow highlighted cells below- pretending 267049 was just cleared?)
Let me know if you guys need any further information or data, thanks a million as always from a struggling intern
I'm wondering how I can copy over data that has been manually entered that is next to automatically populating information. This somewhat builds off of my ' Lookup Order#, Copy Paste data into history, then delete orders ' While that solution addressed my previous needs, I'm pretty sure I have to create a new post for an issue that builds off that macro.
The VBA macro I'm using is below, which copy/pastes all orders that match the "Completed order?" cell from OImport into OrderHistory, then deletes those orders from the OImport table. However, the Summary tab the macro is initiated in has manually entered data (tan highlight) next to the automatically populated data (no highlight) that needs to transfer over to OrderHistory as well. Currently, the user would have to remember where the manual info is, run the macro to clear the order off of Summary and OrderImport, then cut/paste the info into OrderHistory.
Code:
VBA Code:
Const strPassword As String = "Soybean?"
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Address(0, 0) <> "G3" Then Exit Sub
Dim srcWS As Worksheet, desWS As Worksheet, rng As Range
Set srcWS = Worksheets("OrderImport")
Set desWS = Worksheets("OrderHistory")
Call PassProtection(srcWS, False)
Call PassProtection(desWS, False)
Application.ScreenUpdating = False
With srcWS.ListObjects("OImport")
.Range.AutoFilter 1, Target.Value
.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
Set rng = .DataBodyRange.SpecialCells(xlCellTypeVisible).Cells
.DataBodyRange.AutoFilter Field:=1
rng.Delete
End With
Call PassProtection(srcWS, True)
Call PassProtection(desWS, True)
Application.ScreenUpdating = True
End Sub
Sub PassProtection(proWS As Worksheet, PAction As Boolean)
If PAction = True Then
proWS.Protect Password:=strPassword
Else
proWS.Unprotect Password:=strPassword
End If
End Sub
XL2BB:
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
8:400 | Expression | =$A8<>$A9 | text | NO |
A8:A1048576 | Expression | =A8=A7 | text | NO |
F8:F1048576 | Expression | =A8=A7 | text | NO |
G8:G1048576 | Expression | =A8=A7 | text | NO |
H8:H1048576 | Expression | =A8=A7 | text | NO |
I8:I1048576 | Expression | =A8=A7 | text | NO |
J8:J1048576 | Expression | =A8=A7 | text | NO |
While there are XL2BB references to OrderHistory and OrderImport tabs in Lookup Order#, Copy Paste data into history, then delete orders, here is another example
Example: In the XL2BB above, the user wants to clear order 266805 because its item statuses are all completed (pretend they show complete.) They would type in the order# in G3, and the above VBA would transfer everything on OrderImport to OrderHistory, then delete the rows in OrderImport with order# 266805. This would clear the order from Summary, as Summary A8 is a filter to show OrderImport orders sorted by date. But, none of the info from N8:Q10 would clear off of Summary, as its manually entered.
This is where the problem lay, is there any way to set a range equal to Summary columns N:Q for every row with the "Completed order?" number before the With statement
VBA Code:
With srcWS.ListObjects("OImport")
.Range.AutoFilter 1, Target.Value
.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
Set rng = .DataBodyRange.SpecialCells(xlCellTypeVisible).Cells
.DataBodyRange.AutoFilter Field:=1
rng.Delete
End With
WORKBOOK FargoPlan.xlsm | ||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |||
1 | Order# | Line | PO# | Item# | Item Description | BillToID | Bill To Name | ShipDate | Quantity | ShipToID | ShipTo | OrderDate | Type | Pkg Wt | Order UID | Status | Carrier | PlanShip | Sky? | ShipTime | ||
2 | 266804 | -2 | 132419186 | 1065 | 24/1# RAW KERNEL 350 | T53901 | TRADER JOE'S COMPANY | 44721 | 280 | T53808 | TRADER JOE'S / WCD IRVING 5303 | 44698 | Past | 24 | 266804_1065 | Completed | ||||||
3 | 266804 | -1 | 132419186 | 1066 | 24/1# R&S KERNEL 350 | T53901 | TRADER JOE'S COMPANY | 44721 | 350 | T53808 | TRADER JOE'S / WCD IRVING 5303 | 44698 | OilR/S | 24 | 266804_1066 | Scheduled | ||||||
4 | 266804 | -3 | 132419186 | 1067 | 24/1# RNS KERNEL 350 | T53901 | TRADER JOE'S COMPANY | 44721 | 210 | T53808 | TRADER JOE'S / WCD IRVING 5303 | 44698 | OilRNS | 24 | 266804_1067 | Completed | ||||||
5 | 266803 | -2 | 132419184 | 1065 | 24/1# RAW KERNEL 350 | T53901 | TRADER JOE'S COMPANY | 44722 | 420 | T53908 | TRADER JOE'S / WCD LACEY DRY 5103 | 44698 | Past | 24 | 266803_1065 | Completed | ||||||
6 | 266803 | -1 | 132419184 | 1066 | 24/1# R&S KERNEL 350 | T53901 | TRADER JOE'S COMPANY | 44722 | 700 | T53908 | TRADER JOE'S / WCD LACEY DRY 5103 | 44698 | OilR/S | 24 | 266803_1066 | Scheduled | ||||||
7 | 266803 | -3 | 132419184 | 1067 | 24/1# RNS KERNEL 350 | T53901 | TRADER JOE'S COMPANY | 44722 | 560 | T53908 | TRADER JOE'S / WCD LACEY DRY 5103 | 44698 | OilRNS | 24 | 266803_1067 | Scheduled | ||||||
8 | 266861 | -1 | 1012025B | 2604 | 24/1# PAST. PEPITAS | S43001 | SPECIALTY COMMODITIES | 44715 | 610 | S43001 | SPECIALTY COMMODITIES | 44706 | Past | 24 | 266861_2604 | New Past | ||||||
9 | 266861 | -2 | 1012025B | PALLETS | S43001 | SPECIALTY COMMODITIES | 44715 | 9 | S43001 | SPECIALTY COMMODITIES | 44706 | NOT FARGO | ||||||||||
10 | 267049 | -2 | 158966 | 2255 | 25# VAC PAC R&S BAKE | H90301 | LATITUDE 36 FOODS LLC | 44732 | 560 | H90201 | LATITUDE 36 FOODS - OHIO | 44741 | OilR/S | 25 | 267049_2255 | New OilR&S | ||||||
11 | 267049 | -1 | 158966 | 2755 | 25# VAC PAC RNS BAKE | H90301 | LATITUDE 36 FOODS LLC | 44732 | 1120 | H90201 | LATITUDE 36 FOODS - OHIO | 44741 | OilRNS | 25 | 267049_2755 | New OilRNS | ||||||
12 | 267049 | -5 | 158966 | FREIGHT | H90301 | LATITUDE 36 FOODS LLC | 44732 | 1 | H90201 | LATITUDE 36 FOODS - OHIO | 44741 | NOT FARGO | ||||||||||
13 | 267049 | -4 | 158966 | LAB TESTING FEE - RO | H90301 | LATITUDE 36 FOODS LLC | 44732 | 6 | H90201 | LATITUDE 36 FOODS - OHIO | 44741 | NOT FARGO | ||||||||||
14 | 267049 | -3 | 158966 | PALLETS | H90301 | LATITUDE 36 FOODS LLC | 44732 | 24 | H90201 | LATITUDE 36 FOODS - OHIO | 44741 | NOT FARGO | ||||||||||
OrderHistory |
Let me know if you guys need any further information or data, thanks a million as always from a struggling intern