Copy Paste cells to next sheeet

Dear Colleagues please help me I am working to manage vba code but fail. My code work well but one issue involved that data has been pasted on Sheet 2 . I need data on sheet 2 date wise as shown in the image.


Sub CopyRows()
Dim lRow As Integer


lRow = Sheets("Sheet1").Range("H" & Rows.Count).End(xlUp).Row

For Each cell In Sheets("sheet1").Range("F9:F" & lRow)
If Cells(cell.Row, "F").Value = "Cash" Then

Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = _
Sheets("Sheet1").Range("D" & cell.Row).Value
Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = _
Sheets("Sheet1").Range("F" & cell.Row).Value
Sheets("Sheet2").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = _
Sheets("Sheet1").Range("H" & cell.Row).Offset(2, 0).Value

End If
Next cell


For Each cell In Sheets("sheet1").Range("G9:G" & lRow)
If Cells(cell.Row, "G").Value = "Cash" Then
Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = _
Sheets("Sheet1").Range("D" & cell.Row).Offset(-1, 0).Value
Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = _
Sheets("Sheet1").Range("G" & cell.Row).Value

Sheets("Sheet2").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = _
Sheets("Sheet1").Range("H" & cell.Row).Offset(1, 0).Value

End If


Next cell

End sub

Hi,
. Can you give me a clear “”Picture of what you want in the form of a good “Before” and “After” Picture – BUT NOT AS IMAGES
- So that I Then I have some copyable test data to work with.


So 2 sheets please

.
The Before should look just as it does before running of any macro.
.
. The After should be hand filled by you so that it they / looks exactly as you want it to After running of any macro, based on the actual sample data in the Before

. Make sure there is just enough data to demonstrate your need. Remember to desensitize the data if necessary, but use the typical Format type and choose your data carefully so that it can be used to test all possible scenarios.
...
To give these "Picture" - a few ways...( The second is what most people do )

. 1 ) use the Forum Tools in my signature to produce screen shots we can copy to a spreadsheet ( NO IMAGES ! )
. 2 ) Post Files
. 2 a) To Attach a sample workbook:
View Pic
http://www.excelforum.com/members/da...ch-a-file.html
http://tinyurl.com/oenwprw

. 2 b) Send over a file sharing site, such as this free thing
Box Net,
https://www.box.com/
http://tinyurl.com/7chr7u8
. Remember to select Share and give the link / links they give.

. 3 c) Only as a last resort, P.M **( Private Message ) . me and i will reply with my Email Addressee so you can send me a file
** To PM me, click on my name in the left hand margin when you are logged in, the rest should be obvious.

Alan

Hi,
. I will be droping this one off my lists to answer as you have not replied.
. It would be helpful if you no longer want help here to mark the Thread as solved
Thanks
Alan

Hi, Thanks fo the PM..
.
. Sorry, but it is still not clear to me what you want.
. You have given me one File, and some description of what you want
.
. I need to see the situation Before and After the Macro runs that I do for you.
.
. The File that you sent me possibly reflects the “Before” situation ? Or it might be the situation After and so is the After. ?
.
. I need you to send me a two Files.

. File 1: Before
. A File with reduced test data reflecting the situation before a macro runs.

. File 2 After
. In that second file you fill in data manually to reflect the situation after the macro has run. ( based on the actual test data in the before file ) .
. Note: Please fill it in completely. I note that you have written in your explanation
........ “ Same in the remaining acconts
like Accounts Receivable, payable, Rent expense,
Divident, utility expense, advertising expense
+
end of line total from Debit to credit
and credit to debit as well............ “
.. but that does not explain clearly enough. I cannot guess, for example, where to put data in.
. Please fill in your After file completely

. Alan

Thank you very much Mr. Alan, you give me prompt response.
please find herewith attachment 2 files as per your requirements.

Hi internet_imran,
. I think I almost understand. But I am a little confused by your before and After.
Do you want a new sheet to be produced in one File?.
Or
Do you wish after the information is obtained in a new sheet that the original should be deleted ?

.You only have one Sheet in each of the files that you have sent and they are different??

..........................
. Once again :
. - The before should reflect the situation before the macro runs.
.
. The After should reflect the situation after the macro has run.
.
................................................
. I try now guess to what you want is.
. I give you a Before and an After.

.
. Please:
. – confirm that this is what you want
Or
. - correct the files I send, and send them back so that they represent fully what you have before and what you want after the macro runs.

Alan.

Thank you Mr. Alan.

2nd file named Internet_imranafter.xlsm is correct and please focused on it.

Database and Ledger sheets both will be present in this main file. when we run macro on this file all cells or rows on ledger sheet will be deleted firstly and copy cells from database sheet will be pasted on Ledger sheet.

Imran Mahmood

Hello Imran Mahmood....

Originally Posted by internet_imran

........
2nd file named Internet_imranafter.xlsm is correct and please focused on it.
Database and Ledger sheets both will be present in this main file. when we run macro on this file all cells or rows on ledger sheet will be deleted firstly and copy cells from database sheet will be pasted on Ledger sheet......

Ok,
. I will post again if I have more questions or if I have a soulution for you
Good Day to you
Alan Elston

Hi Imran,
. I have started on a solution for You
.
. I expect with a macro it would be best to delete all information in the Ledger ( not just the data ), and start a fresh. So complete information, including all header information, would be generated rather than just the data.
. One consequence of this would be that the order of your Headings would come out slightly different to that in your example File.
. Let me try to explain you with Screeenshot “Pictures”
.
. You have supplied hand filled in output data with the headings in this order:

Using Excel 2007

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS
1	Cash							Rent Expense							Accounts Receivable							Marketing Fees							Accounts Payable							Salaries Expense							Supplies							utility expense							Telephone expense							Advertising expense							Dividends

Ledger

...............
But my program would give this order:

Using Excel 2007

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS
1	Rent Expense							Cash							Accounts Receivable							Accounts Payable							Supplies							Salaries Expense							Utilities Expense							Telephone Expense							Advertising Expense							Dividends							Marketing Fees

Post10Suggestion

.. would that be acceptable ?
. Please reply.
Thankyou
Good day to you
Alan Elston

Imran,

You could approach this task without VBA. I recommend

• Use an Excel Table on 1 Worksheet for your Inputs
• Setup your report as required on a separate Worksheet
• Add helper labels as needed to be used as Criteria with SUMIFS() formulas (You may hide these Rows and Columns)
• Add Structured Reference Formulas as needed to summarize the data to fit your report

Refer to these links:
Excel Tables
Structured References

Hi Goss

Originally Posted by goss

.....
You could approach this task without VBA. I recommend
........

. Thanks for that input. It looks like a very good idea, and probably is a better way to do it than mine:. I am just learning by answering Threads here. Unfortunately I have no idea of Tables, having jumped ahead a bit getting into VB before I really understood more than very basic Spreadsheet stuff.
. I expect Imran will have no chance looking at the Info that you suggested due to language problems!
.
. I think it would certainly be very interesting to see an alternative “spreadsheet solution” of the kind you suggest.
. If possible you could consider giving such a solution. But I appreciate that may be much work, - I really have no idea.
.
. Alternatively, maybe some sort of conditional formatting could be done to compliment my Code solution to give the OP his original required table forms. - At the current stage of my code i am only giving the OP only his required values.
.
. This is consistent with my “VBA Array” approach where I capture everything from the input sheet in one go ( or rather .values) , and then after creating an “internal” VBA output Array, I past that output ( again currently just values ) in one go.
.
. The next step would be for me to include lines to output the format at various code points. This would somewhat go against the basic principal of my method and could be a bit messy.
. . So i am guessing that conditional Formatting might be more efficient, although i also have no experience at all in that area.
.
. Maybe you could comment on that?
. Thanks for adding to the Thread.

Alan

Hi Imran
. I do not understand


File internet_imranAfter_3

Sheet 1
Marketing Fees Credit 300
____________ Credit 1100

Marketing Fees 1.JPG


..........................................


Sheet3
Marketing Fees Credit 300
____________ Debit 1100 ????

Marketing Fees 2.JPG

_ Hi Imran

Originally Posted by internet_imran

...........
1). In Business Accounting T-Call Accounts are worked based
on two columns Debit and Credit side and then Balance from the
Debit and Credit Column. Becuase some Accounts Bal come on Debit
side and some on Credit Side as i highlighted above for you.

_
_ How do I know which Account should be on which side ??



_ is this the answer:

_1 Accounts Payable and Marketing Fees on Balance on Credit side ?

_2 All others on debit side ?

_ is 1 and 2 correct ?
_ Alan

Dear Alan below is a snapshot

Trial Bal.JPG

Hi Imran,

Originally Posted by internet_imran

Dear Alan below is a snapshot....

_
_ I cannot find that on any File that you have given me-

_ Should
_ 1) my Code check That?
_ OR
_ 2) Will it allways be the same as in that Screen shot ?
_
_ If it is always the same as in that screen shot then please post me that infomation agian in an uploaded File, because I cannot Copy your Image. It would be easier for me to copy that Infomation from a file than typing all that infomation out by hand

Alan

Hello Imran,

Originally Posted by internet_imran

........ Debit Side Balance and Credit Side Balance of Ledger Sheet (each account) will come on Trial Balance Sheet one by one.

_ Very Sorry but I still do not understand you fully.

_ My 2 questions were

_ 1) How do I know which side ( Credit or debit ) to put the Bal. ?

_ is this the answer as in your uploaded File ” internet_imranAfter_3” ? :

_ 1a)
Debit Side:
Cash , Rent Expense , Accounts Receivable , Salaries Expense , Supplies , utility expense , Telephone expense , Advertising expense , Dividends

Credit Side:
Marketing Fees , Accounts Payable

OR
_ 1b)
_ is the answer as in your screenshot
Debit Side:
Cash , Rent Expense , Accounts Receivable , Salaries Expense , Supplies , utility expense , Telephone expense , Advertising expense , Dividends

Credit Side:
Marketing Fees , Common Stock , Accounts Payable , Retained Earnings

OR
_ 1c) Can it change from Month to month? If it can change, where should my code look for this new information ? I still do not have this information in any file you have given me

2) Please read again my Post #24. It would be helpful to have the information typed into the Thread or given in a File so I can copy it. I cannot copy information from an Image and so must type everything out by hand as I have done above..

_ I think I am close to understand you now. We just need to clear up how I would definitely know on which side, Credit or Debit, an Account Bal. comes


Best Wishes
Alan

Hi Imran,
_ I think I do understand most of what you have said. And I am very close to a solution for you. I am still not too clear how I would know which side Debit, or Credit an Account balance should go.

_ Let me try again to ask:

_ Shall i assume that,

If present
Cash , Rent Expense , Accounts Receivable , Salaries Expense , Supplies , Utility Expense , Telephone expense , Advertising Expense , Dividends
Will always be on Debit side

And

If present
Marketing Fees , Common Stock , Accounts Payable , Retained Earnings
Will always be on the Credit Side

Is that correct?





Alan

Dear Alan the above statements assumed by you are correct, but these accounts are changed month to month and may be accounts greater than these numbers.

imran

Hi Imran

Originally Posted by internet_imran

Dear Alan the above statements assumed by you are correct, but these accounts are changed month to month and may be accounts greater than these numbers.
...

That is what I thought. And asked I had asked you to confirm that in post # 26 !!!
Post #26:

Originally Posted by Doc.AElstein

.....
_ 1c) Can it change from Month to month? If it can change, where should my code look for this new information ? I still do not have this information in any file you have given me,.,,,,,

_ So how will I or my code know?. How should i or my program get that Information each month?
_ Where would this information be held.

_ My program cannot “guess” each month!

Alan

Thanks Mr. Alan your code is superb. But I need minor change in the subtraction as below picture is attached for you.
Subtract Criteria from Balance.JPG

Hi Imran

Originally Posted by internet_imran

....But I need minor change in the subtraction as below picture is attached for you. .....

_ Yes I understand.

_ I think I may have overlooked that
_ I will correct and Repost or modify the Code later today or tomorrow

Alan

Hi Imran

_ Ok, I have made this correction.

This

Let arrOut(UBound(arrOut(), 1), 3 + DToC) = "=(R" & RR & "C" & CD & ")-(R" & RR & "C" & CC & ")"

Is replaced with this

Let arrOut(UBound(arrOut(), 1), 3 + DToC) = IIf(DToC = 0, "=(R" & RR & "C" & CD & ")-(R" & RR & "C" & CC & ")", "=(R" & RR & "C" & CC & ")-(R" & RR & "C" & CD & ")")

_ Basically If the Final Bal result and the word “Bal” has been shifted ( indicated by Variable DToC being <> 0 ( = 3 ) then the Summation is done the other way around

I have modified the Third Part of the Code here
http://www.excelforum.com/excel-prog...-sheeet-3.html

Please let me know how you get on
Alan

Hi Imran,

Originally Posted by internet_imran

....
Thank you ..... your code is perfect what i need.

You are welcome
Glad we finally got there in the end.

Alan

P.s
If you ara now fully happy with the solution then please

Mark this thread as solved...

ThreadSolved.JPG

Hi

Originally Posted by internet_imran

....are solved by the help of you but second and third is also pending......
So What i do?
is I have to post a new thread for the second and third Assignment after mark this thread as solved?
.......

Yes ...... post a new thread for the second

Yes ...... post a new thread for the third

Yes ...... mark this thread as solved now

But also when you start a thread

you should
1) Put a reference Link ( URL ) to this Thread, so
Start those Threads by writing something like
" This is following on from the code developed here:
http://www.excelforum.com/excel-prog...xt-sheeet.html
... and etc. etc....

and you could
2) also reply again to this Thread and say you are starting a new Thread and give the link to the new Thread in that reply)


Alan

P.s.
Usually you can copy the link ( URL ) from somewhere at the top of your Internet Browser


URL.JPG

Originally Posted by internet_imran

Hi Alan
I format the code but not does not work please help me out.

Thanks

http://www.excelforum.com/excel-prog...ext-sheet.html

Imran

Hi Imran,
Here is my next code for this project
http://www.excelforum.com/excel-prog...ml#post4229760

Alan