Transfer of excel data from a row to another [Solved/Closed]

Report
-
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
-
Hello,
I have this excel sheet with numerous rows having columns A,B,C,D &E.column A is numbers & other columns hav data in text.I want to transfer data of each column against every number to another spreadsheet in a different formati.e for every row there will be one excel sheet with headings A,B,C,D,E in different arrangement & data has to be pasted in front of every heading.Is there any solution?Will be very helpful for me.



9 replies

Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
118
Would you please upload a sample EXCEL file WITH sample data on some shared site like https://authentification.site
AND post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too.


Note: your data need not be to be real data but a good representative of how data looks like
Hi,
Thnks a lot for responding.I hav attached 2 files in speedyshare.Now data of file1 needs to be transfered to file2 in the given manner.colmn A & D are numbers ,rest is in text.File2 is the exact copy of my requirement.File2 is an exmpleof one row thats transferred.In the same way,other rows from file1 need to be transferred to file3,file4,file5,...................
Guess u get the idea.waitng 4 ur feedback...
Vinay.
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
118
Please post the link of both files
Hi,
The links are as under,
http://www.speedyshare.com/files/29107808/File1.xlsx
http://www.speedyshare.com/files/29107811/File2.xlsx
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
118
Hi Vinay,

i download the file 1 but file 2 is not exist, either they delete the file, please upload the file 2 again. and post the link,

Regards,
Rahul
Hi Rahul,
Find the link for the 2nd file,
http://www.speedyshare.com/files/29124058/File2.xlsx

Thanks for responding,
vinay.
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
118
Hi Vinay,

this will be done by the help of Macro,

I will post back the excel sheet with Macro in the link,
https://authentification.site/files/29124214/File1.xls

I had make some changes in your file with following assumption:

1) your data is in Data sheet
2) The format which you want is in the same workbook in sheet format.
3) you have to create one file each row,
4) there is a folder invoice in D drive where all file are saved. or you can change the location. change it in the macro also,

the code is as:

Private Sub MakeForm()

Dim dataWs, FormatWs As Worksheet
Dim lastRow As Long
Dim filename As String


'Rename the worksheet as Data and Format respectively

Set dataWs = Sheets("data")
Set FormatWs = Sheets("format")

FormatWs.Range("A2").Value = dataWs.Range("b1").Value
FormatWs.Range("A3").Value = dataWs.Range("c1").Value
FormatWs.Range("A4").Value = dataWs.Range("d1").Value

lastRow = Range("a" & Rows.Count).End(xlUp).Row
For i = 2 To lastRow
    FormatWs.Range("A1").Value = dataWs.Range("A" & i).Value
    FormatWs.Range("c2").Value = dataWs.Range("B" & i).Value
    FormatWs.Range("c3").Value = dataWs.Range("C" & i).Value
    FormatWs.Range("c4").Value = dataWs.Range("D" & i).Value

    FormatWs.Copy

    Application.DisplayAlerts = False
    With ActiveWorkbook
        filename = Range("a1")
        'set the location where file to be save
        .SaveAs filename:="D:\Invoice\" & filename & ".xls"
        .Close
    End With
    Application.DisplayAlerts = True
Next i

End Sub



Hope it will works for you.

Regards,
Rahul
Hi Rahul,
Thanks a million.This soln is perfect.Actually i am not a software geek.but i pasted my data in your sheet and got my output.only one small problem,how to change the headings in 'FORMAT'file i.e.B,C & D,and put my choice of headings there.Again Thanks man,
Vinay
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
118
Hi Vinay,

I am not able to understand your view,
is B, C, D value are taken from Sheet (Data), then why you want to change them?

Could you explain and what values you want to put in the format file.

Regards,
Rahul
Hi buddy,
No,no, Its ok.I figured it out.Instead of B,C,D, headings ,I wanted Name,Designation & Age which i replaced in your Data sheet.The same got carry forwarded to the Format sheet.This soln. has really taken the pressure off me.Thanks again,stay in touch.I shall trouble you again if anything crop ups in future.
vinay
Hi,
This problem has been solved,thanks to Rahul.This forum is really good for give & take of help without prejudice.Now I know where to approach for such problems.Keep it up folks!
Vinay
Hi,
HRU?I am facing one problem in the files that r saved in the 'Invoice' folder.Everytime i open the files it asks'the file u r trying to open in a different format than specified in the file extension,verify that the file is not corrupted & is from a trusted sorce before opening the file.Do u want to open the file now'.I hav 2 yes & save the file again in proper excel format,otherwise everytime I open the file it asks the same question.Is there some tweaking required somewhere?
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
118
this is happans because you are using excel version above 2003, and the files are saved in excel version 1997 - 2003. you need to change the line in the code,

.SaveAs filename:="D:\Invoice\" & filename & ".xls"

by

.SaveAs filename:="D:\Invoice\" & filename & ".xlsx"
Thanks buddy,
The change took care of the problem!It feels nice to know that HELP is only a email away.gr8 going!
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
118
You are Welcome

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!