How to copy & paste using Macros

[Paul1992]

Basically I have a spreadsheet layed out with columns A to E

In A I have Dates, B Details, C nothing, D Receipts Amount and E Payments Amount.

What I wish to do is copy the Dates Details and Amount for Receipts to a New Tab and the same for the Payments without copying either one of the other. Obviously the level of activity can vary so I can not simply tell it to copy a certain amount (if I sort the data)

If anyone can help it would be great

[PingPing]

You could use the AutoFilter method of the Range object to show only those rows that have Receipt Amounts and then use the SpecialCells(xlCellTypeVisible) property as an input into your copying code. Then do it again, but filtering on Payment Amounts instead.

[royUK]

A PivotTable would probably be a good way, without VBA

[Paul1992]

You could use the AutoFilter method of the Range object to show only those rows that have Receipt Amounts and then use the SpecialCells(xlCellTypeVisible) property as an input into your copying code. Then do it again, but filtering on Payment Amounts instead.

I am not overall that great with VBA I can use the quick shortcut way pretty simple.

Where would I input the (xlCellTypeVisible) part?

[royUK]

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.
To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.


On this page, below the message box, you will find a button labelled 'Manage Attachments'.
Clicking this button will open a new window for uploading attachments.


You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
Alternatively you can click the Attachment Icon to open this page.


To upload a file from your computer, click the 'Browse' button and locate the file.


To upload a file from another URL, enter the full URL for the file in the second box on this page.
Once you have completed one of the boxes, click 'Upload'.


Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Paul1992]

Sample Data.xlsx

Basically this is the data I have from one bank what I want to do is those with the minus signs I want copied into another worksheet and the same with the payments. The only thing is the level of activity can change.

Other ways in which the data can appear is the Receipts will be in Column C and Payments in Column D and I would wish to do the same and split them out

If anyone can help it would be appreciated

[PingPing]

Try this:

Please Login or Register  to view this content.

[Paul1992]

Try this:

Please Login or Register  to view this content.

Sorry about the late reply that is great thank you very much

The only other item is say on the sample data the Minus figures were in the C column and the postive figures stayed in the D column how would I change that to do the same and move to a new sheet?

[PingPing]

Please Login or Register  to view this content.

[Paul1992]

Thanks for that. It has helped immensely

Paul