Data in excel want specific format
- Thread starter Abhijeet
- Start date
This seems to work ok.
Code:
Sub RearrangeData()
Dim NumHeaderRange As Range
Dim NumDataRange As Range
Dim NameRange As Range
Dim ELE As String
Dim ALLO As String
'Where is all of our data?
With Worksheets("Main Data")
Set NumHeaderRange = .Range("C4:C14")
Set NumDataRange = .Range("E4:J14")
Set NameRange = .Range("E15:J16")
End With
Application.ScreenUpdating = False
For i = 1 To NumDataRange.Columns.Count
ELE = NameRange(1, i)
ALLO = NameRange(2, i)
With Worksheets("Final Data")
NumHeaderRange.Copy .Cells(2 + (i - 1) * NumHeaderRange.Rows.Count, "A")
NumDataRange.Columns(i).Copy .Cells(2 + (i - 1) * NumHeaderRange.Rows.Count, "F")
.Range(.Cells(2 + (i - 1) * NumHeaderRange.Rows.Count, "D"), _
.Cells(1 + (i) * NumHeaderRange.Rows.Count, "D")).Value = ELE
.Range(.Cells(2 + (i - 1) * NumHeaderRange.Rows.Count, "E"), _
.Cells(1 + (i) * NumHeaderRange.Rows.Count, "E")).Value = ALLO
End With
Next i
Application.ScreenUpdating = True
End SubWhy did your original post not say anything about having multiple files?? Looping through a folder is a large additional amount of work rather than re-arranging the data.
There are several other threads about looping through filies in folders, as well as excellent samples here:
http://www.rondebruin.nl/win/section3.htm
to show you how to look through folders. There are also threads showing how to handle data of different sizes. Without information on what your data looks like and/or how it can move around, I'm not sure how you expected us to be able to right the code.
Hi Luke i can understand what ur saying but please help me in this problem please give me wide range data is not fix for this range so please tell me for this problemWhy did your original post not say anything about having multiple files?? Looping through a folder is a large additional amount of work rather than re-arranging the data.
There are several other threads about looping through filies in folders, as well as excellent samples here:
http://www.rondebruin.nl/win/section3.htm
to show you how to look through folders. There are also threads showing how to handle data of different sizes. Without information on what your data looks like and/or how it can move around, I'm not sure how you expected us to be able to right the code.
This is the section you'd need to look at:
NameRange is where the ALLO and ELE are at.
NumDataRange is where the data is located.
NumHeaderRange is the row labels for the data range.
Code:
'Where is all of our data?With Worksheets("Main Data")
Set NumHeaderRange = .Range("C4:C14")
Set NumDataRange = .Range("E4:J14")
Set NameRange = .Range("E15:J16")
EndWithNameRange is where the ALLO and ELE are at.
NumDataRange is where the data is located.
NumHeaderRange is the row labels for the data range.
@Abhijeet
You have not provided us with any additional information about the layout of workbook(s) to suggest how we can adapt to a dynamic range. You have not given us samples of these layouts. Simply saying that the range is "dynamic" does not let us code something. Macros need specific instructions to figure out where data is located. We need either a named range, certain phrase, certain structure, etc.
Additionally, you asked for a macro that loops through multiple files. Again, saying this and accomplishing this are very different. How were we supposed to know which folders to loop through? What types of files? Are there multiple worksheets in each file?
As it stands now, I've given you a macro that works solidly for a single sheet. Worst case scenario, you could run it for each of your different books/sheets and change the names and ranges yourself, manually. This forum and site have many excellent resource for learning VBA. While I enjoy helping people, I appreciate when I see the people I help start to take some initiative and try to solve things themselves. You seem to have a history of posting on here asking for solutions to things, often repeatedly for the same thing. This starts to get frustrating for us, and will not help you in the long run, as it doesn't develop your own problem solving abilities.
Please take some time to read about VBA, and try to modify my macro to work on one of your other workbooks. You can walk through the code 1 line at a time by pressing F8, to see how the macro works, if that helps.
You have not provided us with any additional information about the layout of workbook(s) to suggest how we can adapt to a dynamic range. You have not given us samples of these layouts. Simply saying that the range is "dynamic" does not let us code something. Macros need specific instructions to figure out where data is located. We need either a named range, certain phrase, certain structure, etc.
Additionally, you asked for a macro that loops through multiple files. Again, saying this and accomplishing this are very different. How were we supposed to know which folders to loop through? What types of files? Are there multiple worksheets in each file?
As it stands now, I've given you a macro that works solidly for a single sheet. Worst case scenario, you could run it for each of your different books/sheets and change the names and ranges yourself, manually. This forum and site have many excellent resource for learning VBA. While I enjoy helping people, I appreciate when I see the people I help start to take some initiative and try to solve things themselves. You seem to have a history of posting on here asking for solutions to things, often repeatedly for the same thing. This starts to get frustrating for us, and will not help you in the long run, as it doesn't develop your own problem solving abilities.
Please take some time to read about VBA, and try to modify my macro to work on one of your other workbooks. You can walk through the code 1 line at a time by pressing F8, to see how the macro works, if that helps.
Hi Luke Don't take Hyper i can understand you people u are really to help of us