VBA - selecting row to paste in based on customer name

charly75

New Member
Joined
Jul 22, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Learning VBA, so most of what I am doing currently is fairly basic. I am trying to write some code that will paste something from one cell into another cell in a particular column. The row however will depend on the customer that has been selected.

For example, we have updated a sales total for customer A in cell K4 on Sheet1. I want to paste this into cell AV5 on Sheet2 as customer A is in row 5. If I then update the sales total for Customer C (also in cell K4 on Sheet1), I would need to paste into cell AV7.

I have a look up table which tells me which row each customer is in, and I have put this info into cell P3 on my original spreadsheet. I had hoped there was a way that I could specify the row number based on the number in cell P3 on Sheet1, but I seem to be running into errors with whatever I try. The rest of the code works if I specify the actual cell number, but not if I try and create a cell reference based on an entry into P.

Any help would be gratefully received!
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,974
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Maybe
VBA Code:
Sheets("Sheet2").Range("AV" & Sheets("Sheet1").Range("P3")).Value = Sheets("Sheet1").Range("K4").Value
 

charly75

New Member
Joined
Jul 22, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
That seems to work perfectly - thank you so much! (I think I was trying to over-complicate it!!!)
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,974
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,127,387
Messages
5,624,389
Members
416,026
Latest member
melvic69

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