Excel 2007 - Copying cells from 1 sheet to another

[Kesh]

I have a sheet which contains several columns. The sheet displays blocks of data with a total at the end of each block.

I want to copy the cells from the sheet (named Citi) to Sheet1 from column D but only from those cells with a value. The value occurs several times but I need to copy it once and paste it to cell B8 of Sheet1.

Then I need to copy the Total in in column O and in the row following the one where the number appeared the last time in D.

Can anybody help me please?

Thank you in anticipation

Kesh
Account TypeAccount NumberCompany CodeLoan NumberClient Last NameClient First NameSINSettlement DateNon-Reversal TradeReversal TradeFund CodeFund NameTotal Assigned UnitsTotal UnitsMarket ValueCASH41846904AGFBASTERMAJIJACK4996954762012/08/10 0:00Y334AGF Precious Metals Fund DSC125.651134.453598.64CASH41846904AGFBASTERMAJIJACK4996954762012/08/10 0:00Y681AGF Canadian Stock Fund DSC154.138164.8996846.79CASH41846904AGFBASTERMAJIJACK4996954762012/08/10 0:00Y###AGF Emerging Markets Class874.458906.79314174.96CASH41846904AGFBASTERMAJIJACK4996954762012/08/10 0:00Y###AGF Canadian Large Cap Dividend Class DSC1560.0741612.72419625.7344246.12CASH48893642AGF3163169HOLTBYDESMOND JOHN6102797882012/08/10 0:00Y334AGF Precious Metals Fund DSC218.009218.0096243.78CASH48893642AGF3163169HOLTBYDESMOND JOHN6102797882012/08/10 0:00Y916AGF Global Real Estate Equity Class DSC593.636593.6376084.76CASH48893642AGF3163169HOLTBYDESMOND JOHN610279788931AGF American Growth Class DSC216.856245.74436.8816765.42

 

[vbaInet]

You would have been better off asking this question in the Excel section of the forum.

If you're copying from Column D and onwards and Sheet1 has no data, it would be best to make a copy of Citi, rename it as Sheet 1 and delete columns A to C. I don't understand what you mean by copying only those cells with a value??

By the way, the pasted rows are unreadable. Upload a spreadsheet.

 

[Kesh]

Thank you for your reply.

Let me clarify.

I need to copy data from column D only. For some rows the cells in Column D are blank. I do not need those cells. I need only the cells that contain a value.

Secondly Sheet 1 is where I am preparing a report with several other columns. The data will be populated in Sheet1 based on the value copied from column D using VLOOKUP.

I have written this code so far but it is not achieving the required results.

Sub CopyMacro()
Sheets("Citi").Select
ActiveCell.Select
Application.CutCopyMode = False
ActiveCell.Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Sheet1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub


The logic that I need to translate into code is this:

Read cell D2,
If D2 is blank, read next (D3)
Loop until it is not blank
If D2 is not blank, copy and paste to B8 in Sheet1

I hope I have been able to clarify

Thank you

kesh

 

[vbaInet]

I still need to see a spreadsheet with the before and after look.

 

[Kesh]

Thank you

I have been able to write part of the code up to now

Kesh

 

[vbaInet]

And the copy and paste works as you expect it to?