Copy and paste based on cells values (VBA?)

Jackman1

New Member
Joined
Jan 13, 2015
Messages
26
Hi Guys,

A massive thank you to everyone involved in my previous posts and help to others, also enabling me to investigate to find answers to my queries . My Excel knowledge is slowly creeping up, helping me to improve the spreadsheets I work on (in some cases improving 4 hours work into 10 minutes!).

I have an issue with a spreadsheet which is exported from a source where the format cannot be changed. Currently in order for this spreadsheet to work when I import into my main document, I have to manually change all items in the example below from the left image to the right image (currently 100 + "Products"). The change is to put the "Code" onto the same line as the "Branch" below then entry, and repeat where applicable (Branch will be 1 to 15 lines)

This action allows a =SUMIFS formula to work (this does work really when so I'm hoping there could be maybe a VBA code (which I can then assign to Macro enabled button) can be written to avoid the manual entry and speed up the process immensely and avoid potential human error.

Any questions please ask.

Thank you
Shaun
 

Attachments

  • Example image.jpg
    Example image.jpg
    247.4 KB · Views: 12

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
How about
VBA Code:
Sub Jackman()
   Dim Rng As Range
   
   With Range("B3", Range("B" & Rows.Count).End(xlUp))
      .Replace "Branch", "", xlWhole, , False, , False, False
      For Each Rng In .SpecialCells(xlBlanks).Areas
         Rng.Value = Rng.Offset(-1, 1).Resize(1).Value
      Next Rng
   End With
End Sub
 
Upvote 0
Hi,

Please check below code:

VBA Code:
Sub changeFormat()
    Dim lastRow As Integer, rowno As Integer
    Dim codeToCopy As String
    
    With Sheets("Sheet1")
        lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        
        For rowno = 3 To lastRow
            If .Range("B" & rowno) = "Product" Then
                codeToCopy = .Range("C" & rowno)
            Else
                .Range("B" & rowno) = codeToCopy
            End If
        Next
    End With
End Sub
 
Upvote 0
Solution
Works a treat Fluff!

Thank you so much, BTW I think your code was written quicker than my message :)

Really appreciate it.

Thanks
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

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