Copy and Paste Value to multiple cell range then loop (Excel)

j_saints

New Member
Joined
Sep 21, 2020
Messages
3
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
Hello all!

I have a sample invoice detail that was converted into Excel but I will need to extract the values from the customer invoice. In order to do this, I will first need to copy and paste the location to column C

1600711852317.png


Here's what it looks like when it's done (I'd sort this by column C so it is grouped by company)
1600712057349.png



It would really take time to do copy+paste manually so I was wondering if there's a way in Excel to automate this step. I'm quite new to VBA and still learning, thankfully I found this forum for guidance. I tried recording the process via macro but not so sure how to make it loop if I'm doing it for 100+ companies.

Any help would be much appreciated! 🙏 Thank you all!
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,314
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Will each section always begin with Gross Wages?
 

j_saints

New Member
Joined
Sep 21, 2020
Messages
3
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
Hi & welcome to MrExcel.
Will each section always begin with Gross Wages?

Hello, thank you so much for welcoming me! Yes, each section always begin with Gross Wages.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,314
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
Sub jsaints()
   Dim Rng As Range
   
   For Each Rng In Range("B:B").SpecialCells(xlConstants, xlNumbers).Areas
      If Rng.Offset(, -1).Resize(1) = "Gross Wages" Then
         Rng.Offset(, 1).Value = Rng.Offset(Rng.Count - 1, -1).Resize(1)
      End If
   Next Rng
End Sub
 

j_saints

New Member
Joined
Sep 21, 2020
Messages
3
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
Ok, how about
VBA Code:
Sub jsaints()
   Dim Rng As Range
  
   For Each Rng In Range("B:B").SpecialCells(xlConstants, xlNumbers).Areas
      If Rng.Offset(, -1).Resize(1) = "Gross Wages" Then
         Rng.Offset(, 1).Value = Rng.Offset(Rng.Count - 1, -1).Resize(1)
      End If
   Next Rng
End Sub

Wow, it worked!! Thank you so much for the help Fluff and kudos to this site for helping and guiding beginners like me!! I really appreciate it!!! 🙏 💯
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,314
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,359
Messages
5,601,157
Members
414,431
Latest member
JustmemyselfandI

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