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

Question

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.



Configuration: Windows XP / Internet Explorer 8.0

RWomanizer · Answer

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

RWomanizer · Answer

Please post the link of both files

RWomanizer · Answer

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

RWomanizer · Answer

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

RWomanizer · Answer

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

vinay · Answer

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

RWomanizer · Answer

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"

vinay · Answer

Thanks buddy,
   The change took care of the problem!It feels nice to know that HELP is only a email away.gr8 going!

RWomanizer · Answer

You are Welcome

Transfer of excel data from a row to another

9 responses

Similar discussions

Subscribe To Our Newsletter!