• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

selected cell records [SOLVED]

F

fracertrance

New Member
I have designed in excel-2007 an invoice

kind of format.

There are certain cell details but at

different positions which at present I copy and paste

it on the other sheet in a row form.

Any method whereby after making all the entries

in the Invoice sheet , I click a button and

the cells gets pasted in a row form in the

other sheet, starting from A2 since the first row

has the heading name. The existing cells are


D8

D5

D6

F5

F6

D14

D15

D16

D17

D18

D19

D20

H24

I24


Info in A2 will be of D8

B2 will be D5

C2 will be D6

Second Invoice the info gets pasted from

A3,B3,C3

and so on.

At the end of month I will get the entire list of

Invoices I have made with a ready reference records.


Slightly difficult but I donot know if it is possible

in Excel.
 
Im not 100% I understand your request...


right now, your current method is arranging the data in the same column (like your example)...but you instead want them to be in the same row, going across different columns?!


if so, try copying the data as shown in your example, go to A2, Paste Special, and use the Transpose option.


again, my apologies if i am misunderstanding you.
 
Sorry! There is a slight miss understanding.

In the Invoice sheet I have information at the

above mentioned cells. I have just listed down

the cells for information purpose. What I want is

at the click of a button the information in the

above mentioned cells should get transferred in the

other sheet - row wise starting from A2. So in short starting from

A2 the above cells will be like


D8,D5,D6,F5,F6,D14,D15,D16,D17,D18,D19,D20,H24,I24.


The info from the Invoice sheet will get transferred to

the next sheet and will be pasted as shown above.

Now when I make a new Invoice the next record will

be pasted from A3 and so on.

At the end of the month I will get the record of all

the Invoices I made.


I hope this is clear or just reply me back.

thanks
 
Hi, fracertrance!


Does this post regarding the use of TRANSPOSE function helps?

http://chandoo.org/forums/topic/importing-txt-to-excel#post-124792


What I still don't understand is if you have these formulas at these cells:

A2: =D8

A3: =D5

...

A15: =I24

or if you just have these values at these cells:

A2: D8

A3: D5

...

A15: I24


However, please consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.


Regards!
 
Hi FranceTrance


I think I understand your post OK. What you are trying to do is place data from a template which is all over the place in one line of a summary sheet. So you are trying to build a database from the information which is pushed into your template. The following assumes you have two sheets with the sheet object sheet1 and sheet2.


'Option Explicit

Option Base 1

Sub testo()

Dim ar As Variant

Dim arr As Variant

Dim i As Integer

Dim lr As Long

Application.ScreenUpdating = False


lr = Sheet2.Range("A" & Rows.Count).End(xlUp).Row + 1

ar = Array("D8", "D5", "D6", "F5:F6", "D14:D20", "H24", "I24") 'Trap Range

arr = Array(1, 2, 3, 4, 6, 13, 14) 'Trap Column numbers


For i = 1 To UBound(ar)

Sheet1.Range(ar(i)).Copy

Sheet2.Cells(lr, arr(i)).PasteSpecial Transpose:=True

Next i

Application.ScreenUpdating = True

End Sub'


As you may have data which is blank it is prudent to trap the last used row of the Database sheet before you go into moving the data. The data will be dropped in from Col A. The two Variants are used to align your data. As SirJB7 said a file would be most helpful.


This should point you in the right direction at any rate.


Take care


Smallman
 
To clarify one more time In sheet1 I have information in the

following cells as below this is just an eg.


D8 - ABC

D5 - 123

D6 - 456

F5 - 789

F6 - TRT

D14 - 222

D15 - 333

D16 - 444

D17 - 555

D18 - 666

D19 - 777

D20 - 888

H24 - XYZ

I24 - WER


After clicking the command button in sheet1 all the above information

gets pasted in sheet2 at the following cells


A2 - ABC

B2 - 123

C2 - 456

D2 - 789

E2 - TRT

F2 - 222

G2 - 333

H2 - 444

I2 - 555

J2 - 666

K2 - 777

L2 - 888

M2 - XYZ

N2 - WER


When I make the next new entry in sheet1 and click the

command button , its info should be pasted from A3 TO N3

and so on.
 
To clarify one more time In sheet1 I have information in the

following cells as below this is just an eg.


D8 - ABC

D5 - 123

D6 - 456

F5 - 789

F6 - TRT

D14 - 222

D15 - 333

D16 - 444

D17 - 555

D18 - 666

D19 - 777

D20 - 888

H24 - XYZ

I24 - WER


After clicking the command button in sheet1 all the above information

gets pasted in sheet2 at the following cells


A2 - ABC

B2 - 123

C2 - 456

D2 - 789

E2 - TRT

F2 - 222

G2 - 333

H2 - 444

I2 - 555

J2 - 666

K2 - 777

L2 - 888

M2 - XYZ

N2 - WER


When I make the next new entry in sheet1 and click the

command button , its info should be pasted from A3 TO N3

and so on.
 
Hi FranceTrance


Just so we are clear about the following, the code above which I gave you yesterday does exactly what you describe. I don't want there to be any confusion, as I have tested it on my machine.


If you don't believe me, open a fresh version of XL and on Sheet1 put your data in the cells you describe. Paste the macro into a regular module. Now run the macro. It will push your data into Sheet2 row 2. Now run it again. It will push your data into Sheet2 Row 3.


If you follow the simple steps I have outlined you will have your answer and be happily on your way :)


Take care


Smallman
 
Is it possible for you to send me the excel file with inbuilt

macro code.

I am trying , but gettin some error message.

sorry! for the trouble and

thanks
 
FranceTrance


What is your email address? As an example write it like this;


YourEmailName AT yahoo.com


Don't use the @ symbol and I will send you the file. You will be happy with the results.


Take care


Smallman
 
Hi, Smallman!

Maybe you want to share your solution with the community, so as people who read this would have the issue or question and the solution or answer as well.

Regards!
 
Hi SirJB7


I have shared my solution. It is all above. All of the code and all of the workings and all of the information to recreate the workbook are in the thread.


Seemed like a slam dunk to me but FT needs to see the file and I offeed to email it. I don't have a cloud account perhaps you would like to post it FT once you get it?


Take care


Smallman
 
Hi FranceTrance


I have sent the file. If you could mention if you got it whenever that would be great.


Take care


Smallman
 
Hi


I had a thought and I have put a copy of the file below if anyone wants to see workings.


http://www.ozgrid.com/forum/showthread.php?t=181110&p=676525#post676525


Take care


Smallman
 
You must log in or register to reply here.
Back
Top