Manipulate the appearance of a spreadsheet in VBA

[CaptainGravyBum]

Hello,
Hope you can help. I have data which has to be copied into Excel for an import and I need to find a way to manipulate the sheet to show only the required data in each row.
The number of rows will change every time this is pasted in and I need the Customer Code shown in B1 and B5 to appear to the left of each Con No row. The header rows need to be removed as well if possible, because I can add them in at the top if needed. Customer ref should always be in column B, but if copied incorrectly could appear in Column A, so something to cover both eventualities would be beneficial.
Oh, and when the row containing the Customer Code has been used, it can also be removed.

I'm not sure how to write a macro to find the cells with customer ref and insert the information on the rows where it is needed.

Sorry it's just an image, my company policies do not allow the XL2BB Add-in to run.

 

[mumps]

Could you please upload a copy of the file that is generating the error?

 

[CaptainGravyBum]

I have uploaded the source file here and I think I now know what the problem is. When the data is exported it usually has a couple of pictures that didn't show in my first file, these are copied across even when I paste values only and they are stopping the macro from running.
If I delete them, it works fine.
I have included the pictures in the latest file, I did try to do something like Activesheet.Shapes.Delete after the file is pasted, but it doesn't do anything.

 

[mumps]

I believe that the shapes are not the problem. What seems to be the problem is the text "#VALUE!" in cell A8. I deleted that text without deleting the shape and the macro worked properly.

 

[CaptainGravyBum]

Hi @mumps
I ran some more tests and found that the "image" as I thought it was, actually showed in reality as a formula.

I've used the following code to find and remove those items before running the script you provided:

Dim ws As Worksheet
Dim cell As Range

' Set the worksheet
Set ws = ActiveSheet ' You can change this to reference a specific worksheet if needed

' Loop through each cell in the worksheet
For Each cell In ws.UsedRange
' Check if the cell formula is "Picture"
If cell.FormulaR1C1 = "Picture" Then
' Clear the contents of the cell
cell.ClearContents
End If
Next cell

Seems to do the job, thanks again for your help.

 

[mumps]

My pleasure.