Pasting data from Sheet A to a specific named row on Sheet B

mackemforever

New Member
Joined
Jun 29, 2020
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
This will require a bit of explaining.

Sheet A is my data entry sheet.
Sheet B is my master sheet.

Sheet B contains a list of all of my customers, complete with their most recent service.

Sheet A contains entry forms for new customers and new services.

The new service form on Sheet A allows me to select which customer it is via a drop-down menu that pulls the list from Column A on Sheet B.

When I have filled in the information in the new service form I want to be able to run a macro that will copy that information and paste it in to the row for that customer. So for example if I have selected customer Paul, his information is on Row E so the information from the new service sheet needs to be pasted in to Row E.

I've been playing around with this for a while and am absolutely stumped. How on earth would I achieve this?

Thanks.
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,144
It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your two sheets and explain in detail what you want to do referring to specific cells, rows, columns and sheets.
 

mackemforever

New Member
Joined
Jun 29, 2020
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
Here you go. So essentially when the macro is run I want it to copy the service type & date, locate the column that matches the selected customer and paste it in that column.

Customer Management.xlsm
EFGH
1CustomerDarren
2Service TypeSingle Stage Polish
3Service Date28/06/2020
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
New Info
Cells with Data Validation
CellAllowCriteria
F1List=Master!$A$2:$A$146
F2List=$A$45:$A$49


Customer Management.xlsm
ABCDEF
1NameAddressPhone NumberEmailService TypeService Date
2Dan Summerfield10 hollisters drive, bs13 0ey07983179652dansummerfield1995@gmail.com
3Darren2 moor croft drive, bs30 7db07720238088daz891@outlook.com
4Gary108 moravian road, bs1507453 971956gary143@blueyonder.co.uk
5JackKings Court07947408881gwsp2000@gmail.com
6Matt BurrowsNorth Nibley, GL11matt@mattburrows.co.uk
7Paul Wainwright26 fouracre crescent, bs16 6ps07961502889pwainwright84@yahoo.co.uk
8Phil Britton11 Charnhill Brow, BS16 9JW07528622815philipbritton@sky.com
Master
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
Do you always want to paste the data into columns E & F overwriting anything that may already be there?
 

mackemforever

New Member
Joined
Jun 29, 2020
Messages
6
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Do you always want to paste the data into columns E & F overwriting anything that may already be there?

Yes. The data will be pasted to a table on the customers own page, pasting it to the next available row (this is already set up), but on the Master sheet I want it to always paste in the same place as a record of the most recently entered service.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
Sub mackemforever()
   Dim Fnd As Range
   
   With Sheets("New Info")
      Set Fnd = Sheets("Master").Range("A:A").Find(.Range("F1"), , , xlWhole, , , False, , False)
      If Not Fnd Is Nothing Then
         Fnd.Offset(, 4).Resize(, 2).Value = Application.Transpose(.Range("F2:F3").Value)
      Else
         MsgBox .Range("F1").Value & " not found"
      End If
   End With
End Sub
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,144
Another approach: Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter the data in F1, F2 and F3 in that order and press the RETURN key.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F3")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim fnd As Range
    Set fnd = Sheets("Master").Range("A:A").Find(Range("F1"), LookIn:=xlValues, lookat:=xlWhole)
    If Not fnd Is Nothing Then
        Range("F2:F3").Copy
        Sheets("Master").Range("E" & fnd.Row).PasteSpecial Transpose:=True
        Application.CutCopyMode = False
    End If
    Application.ScreenUpdating = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,980
Messages
5,599,160
Members
414,295
Latest member
Dolenhil

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
Top