VBA to copy over manual entry cells that are adjacent to imported data

TheGrandPooba

New Member
Joined
Jul 1, 2022
Messages
12
Office Version
  1. 365
Platform
  1. 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:
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:
WORKBOOK FargoPlan.xlsm
ABDFGHIJKLNOPQRS
1
2When shipped:
3Completed order?2670491. Enter =now() in ShipTime
4Y=Item done2. Press F2, F9, Enter on cell
5N=Item not done3. Enter order# to clear
6Orders Sorted by ShipDaten=Item not assigned a lot4. Cut/paste Carrier-ShipTime to History
7All OrdersItemTypeBillToShipToPO#OrderDateShipDateStatusQC?Carrier?PlanShip?@Sky?ShipTime
82668051065PastTRADER JOE'S COMPANY TRADER JOE'S HARTFORD 1324191855/17/226/9/22Completed#N/A7/7 1:57PM
92668051066OilR/STRADER JOE'S COMPANY TRADER JOE'S HARTFORD 1324191855/17/226/9/22Scheduled#N/ABuyer7/7Yes7/7 1:57PM
102668051067OilRNSTRADER JOE'S COMPANY TRADER JOE'S HARTFORD 1324191855/17/226/9/22Processingn
112668351831PastNEWARK NUT COMPANY NEWARK NUT COMPANY 10707025/20/226/15/22Completedn
122668353570DryNEWARK NUT COMPANY NEWARK NUT COMPANY 10707025/20/226/15/22Completed#N/A7/7 2:08PM
132668358630OilR/SNEWARK NUT COMPANY NEWARK NUT COMPANY 10707025/20/226/15/22Scheduled#N/A
142668358730OilRNSNEWARK NUT COMPANY NEWARK NUT COMPANY 10707025/20/226/15/22Scheduledn
152669291066OilR/STRADER JOE'S COMPANY TRADER JOE'S / WCD CHINO 5043 1326127636/2/226/23/22Scheduled#N/A
162669291067OilRNSTRADER JOE'S COMPANY TRADER JOE'S / WCD CHINO 5043 1326127636/2/226/23/22Scheduledn
172669311065PastTRADER JOE'S COMPANY TRADER JOE'S / WCD STOCKTON 5053 1326127646/2/226/23/22Completedn
182669311066OilR/STRADER JOE'S COMPANY TRADER JOE'S / WCD STOCKTON 5053 1326127646/2/226/23/22Scheduled#N/A
192669311067OilRNSTRADER JOE'S COMPANY TRADER JOE'S / WCD STOCKTON 5053 1326127646/2/226/23/22Scheduledn
Summary
Cells with Conditional Formatting
CellConditionCell FormatStop If True
8:400Expression=$A8<>$A9textNO
A8:A1048576Expression=A8=A7textNO
F8:F1048576Expression=A8=A7textNO
G8:G1048576Expression=A8=A7textNO
H8:H1048576Expression=A8=A7textNO
I8:I1048576Expression=A8=A7textNO
J8:J1048576Expression=A8=A7textNO

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
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?)
WORKBOOK FargoPlan.xlsm
ABCDEFGHIJKLMNOPQRST
1Order#LinePO#Item#Item DescriptionBillToIDBill To NameShipDateQuantityShipToIDShipToOrderDateTypePkg WtOrder UIDStatusCarrierPlanShipSky?ShipTime
2266804-2132419186106524/1# RAW KERNEL 350 T53901 TRADER JOE'S COMPANY 44721280T53808 TRADER JOE'S / WCD IRVING 5303 44698Past24266804_1065Completed
3266804-1132419186106624/1# R&S KERNEL 350 T53901 TRADER JOE'S COMPANY 44721350T53808 TRADER JOE'S / WCD IRVING 5303 44698OilR/S24266804_1066Scheduled
4266804-3132419186106724/1# RNS KERNEL 350 T53901 TRADER JOE'S COMPANY 44721210T53808 TRADER JOE'S / WCD IRVING 5303 44698OilRNS24266804_1067Completed
5266803-2132419184106524/1# RAW KERNEL 350 T53901 TRADER JOE'S COMPANY 44722420T53908 TRADER JOE'S / WCD LACEY DRY 5103 44698Past24266803_1065Completed
6266803-1132419184106624/1# R&S KERNEL 350 T53901 TRADER JOE'S COMPANY 44722700T53908 TRADER JOE'S / WCD LACEY DRY 5103 44698OilR/S24266803_1066Scheduled
7266803-3132419184106724/1# RNS KERNEL 350 T53901 TRADER JOE'S COMPANY 44722560T53908 TRADER JOE'S / WCD LACEY DRY 5103 44698OilRNS24266803_1067Scheduled
8266861-11012025B 260424/1# PAST. PEPITAS S43001 SPECIALTY COMMODITIES 44715610S43001 SPECIALTY COMMODITIES 44706Past24266861_2604New Past
9266861-21012025B PALLETS S43001 SPECIALTY COMMODITIES 447159S43001 SPECIALTY COMMODITIES 44706NOT FARGO
10267049-2158966225525# VAC PAC R&S BAKE H90301 LATITUDE 36 FOODS LLC 44732560H90201 LATITUDE 36 FOODS - OHIO 44741OilR/S25267049_2255New OilR&S
11267049-1158966275525# VAC PAC RNS BAKE H90301 LATITUDE 36 FOODS LLC 447321120H90201 LATITUDE 36 FOODS - OHIO 44741OilRNS25267049_2755New OilRNS
12267049-5158966FREIGHT H90301 LATITUDE 36 FOODS LLC 447321H90201 LATITUDE 36 FOODS - OHIO 44741NOT FARGO
13267049-4158966LAB TESTING FEE - RO H90301 LATITUDE 36 FOODS LLC 447326H90201 LATITUDE 36 FOODS - OHIO 44741NOT FARGO
14267049-3158966PALLETS H90301 LATITUDE 36 FOODS LLC 4473224H90201 LATITUDE 36 FOODS - OHIO 44741NOT FARGO
OrderHistory


Let me know if you guys need any further information or data, thanks a million as always from a struggling intern
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top