Formula for macro to identify and move cells to produce report

D

davidwh000

New Member
Joined
Jul 18, 2010
Messages
24
I have a spreadsheet to work out VAT recording and am trying to improve a macro I have to do reports. I need to select a section of entries and then look for the cell with OOS in it, insert 4 rows of cells only across the selected area ensuring that the original data is kept intact.
The data looks like this and there are fixed sections below the text and to the right which I don't want to be overwritten
VAT Return Workings Period 01/10/2013 31/12/2013



OUTPUTS Standard and Zero Rated

Payment Date Details Invoice Gross VAT Rate Net VAT


41556 Report/conference call 006 1200 20 1000 200
41563 Retainer 007 750 0 750 0
41569 Expenses claimed 008 75.6 20 63 12.6
41569 Expenses claimed 008 24 20 20 4
41563 Interest 0.82 OOS 0.82 0

Any help greatly appreciated
Thanks
David
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
your payment date is a number - fix that and for this data - show us what you would like to see please
 
Upvote 0
oldbrewer said:
your payment date is a number - fix that and for this data - show us what you would like to see please
Click to expand...
Thanks for the response.
This is the result I need to achieve[TABLE="width: 1040"]
<tbody>[TR]
[TD="colspan: 2"]VAT Return Workings[/TD]
[TD][/TD]
[TD]Period[/TD]
[TD="align: right"]01/10/2013[/TD]
[TD="align: right"]31/12/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]
clip_image001.gif

<tbody>
</tbody>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]OUTPUTS[/TD]
[TD]Standard and Zero Rated[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Payment Date[/TD]
[TD]Details[/TD]
[TD]Invoice[/TD]
[TD]Gross[/TD]
[TD]VAT Rate[/TD]
[TD]Net[/TD]
[TD]VAT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]09/10/2013[/TD]
[TD]Report/conference call[/TD]
[TD]006[/TD]
[TD]£1,200.00[/TD]
[TD]20[/TD]
[TD]£1,000.00[/TD]
[TD]£200.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]16/10/2013[/TD]
[TD]Retainer[/TD]
[TD]007[/TD]
[TD]£750.00[/TD]
[TD]0[/TD]
[TD]£750.00[/TD]
[TD]£0.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]22/10/2013[/TD]
[TD]Expenses claimed from LeM[/TD]
[TD]008[/TD]
[TD]£75.60[/TD]
[TD]20[/TD]
[TD]£63.00[/TD]
[TD]£12.60[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]22/10/2013[/TD]
[TD]Expenses claimed from LeM[/TD]
[TD]008[/TD]
[TD]£24.00[/TD]
[TD]20[/TD]
[TD]£20.00[/TD]
[TD]£4.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]£2,049.60[/TD]
[TD][/TD]
[TD]£1,833.00[/TD]
[TD]£216.60[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Payment Date[/TD]
[TD]Details[/TD]
[TD]Invoice[/TD]
[TD]Gross[/TD]
[TD]VAT Rate[/TD]
[TD]Net[/TD]
[TD]VAT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]16/10/2013[/TD]
[TD]Interest[/TD]
[TD][/TD]
[TD]£0.82[/TD]
[TD]OOS[/TD]
[TD]£0.82[/TD]
[TD]£0.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]£0.00[/TD]
[TD][/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col><col></colgroup>[/TABLE]

This is the macro I am currently using but I have to manually change the cell references if the input data has more entries or if the OOS references increase. This includes the macro part which formats the cells as dates and does some calculation via the sum function. It requires a lot of manual editing if new data is added to the receipts and payments sheets. Sub MoveOSSandformat()
' move OOS Receipts
Sheets("Report").Select
Range("A14:H15").Select
'cut selection from start of OOS and paste 4 cells down
Selection.Cut
Range("A19").Select
ActiveSheet.Paste
Range("B7:H7").Select
Selection.Copy
'copy heading range and paste 1 cell up from earlier paste
Range("B18").Select
ActiveSheet.Paste

Range("A40").Select

' move OOS Payments
Range("A45:H52").Select
'cut selection from start of OOS and paste 4 cells down
Selection.Cut
Range("A49").Select
ActiveSheet.Paste
Range("B38:H38").Select
Selection.Copy
'copy heading range and paste 1 cell up from earlier paste
Range("B48").Select
ActiveSheet.Paste
Columns("B:B").Select
Application.CutCopyMode = False
Selection.NumberFormat = "m/d/yyyy"
Range("A40").Select

' SumColumns Macro
Range("E17").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)"
Range("G17").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)"
Range("H17").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)"
Range("E24").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
Range("G24").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
Range("H24").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
Range("E72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-32]C:R[-1]C)"
Range("G72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-32]C:R[-1]C)"
Range("H72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-32]C:R[-1]C)"
Range("E92").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-16]C:R[-1]C)"
Range("G92").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-16]C:R[-1]C)"
Range("H92").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-16]C:R[-1]C)"
Range("H92,G92,E92,E72,G72,H72,H24,G24,E24,E17,G17,H17").Select
Selection.Font.Bold = True
Range("E7:E92").Select
ActiveWindow.SmallScroll Down:=-51
Range("E7:E92,G7:H92").Select
Range("G7").Activate
Selection.NumberFormat = "$#,##0.00"
With Selection
.HorizontalAlignment = xlCenter
End With
Range("A2").Select
End Sub
 
Upvote 0
if your data is in the form of a text block for each row separated by blanks, eg 41556 Report/conference call 006 1200 20 1000 200
I do not believe it is possible to automatically break it up into individual cells. Do you have any control of the original data - does it come from a database?
 
Upvote 0
No, each entry is in its own cell. The copy and past at the top is not representative, not sure what went wrong there. The data is pulled in from other tabs in the same workbook. Thanks. David
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

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