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.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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.
 
Upvote 0
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
 
Upvote 0
Do you always want to paste the data into columns E & F overwriting anything that may already be there?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

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