Jan 2nd, 2014, 07:47 AM
#1
Thread Starter
Lively Member
[RESOLVED] Macro should copy data from One sheet & paste in different format in another sheet
Dear All,
I am very new to VBA, hence I need your help to do this task.
Currently I have a sheet called "Sample_Raw_Data" where I have all raw details.
Now I want to copy and paste each row with below format in a different sheet
======================================================================================
Group Medium Subject State District City Sales SubRegion1 Fee Details Year Total Fees
======================================================================================
Can someone please let me know the VBA code to do this task?
I have attached sample excel file with sample results in the attachment.
Thank you so much for your help in advance
Best Regards
Amrutha
Attached Files
Jan 2nd, 2014, 08:36 AM
#2
Re: Macro should copy data from One sheet & paste in different format in another shee
It looks like you want the 2nd sheet to be a summary sheet. Please provide the explanation of how it should be grouped/summarized, thanks!
Jan 2nd, 2014, 09:40 AM
#3
Thread Starter
Lively Member
Re: Macro should copy data from One sheet & paste in different format in another shee
Hi vbfbryce,
Thank you so much for responding. In "Sample_Raw_Data" sheet first row was copied and pasted into "Macro Results" sheet by splitting 25 rows. I want each row in "Sample_Raw_Data" should copied and splitted into 25 rows for each year and each fee details.
You can see the 1st row sample data in the existing "Macro Results" sheet.
Jan 2nd, 2014, 11:28 AM
#4
Re: Macro should copy data from One sheet & paste in different format in another shee
Ah, I looked at it wrong. You mean you want to take one row in Sample Raw Data and turn it into 25 or so rows in Macro, right?
Jan 2nd, 2014, 11:31 AM
#5
Thread Starter
Lively Member
Re: Macro should copy data from One sheet & paste in different format in another shee
Yes Sir. You're right, but it should be split based on years and fee details.
Jan 2nd, 2014, 11:38 AM
#6
Thread Starter
Lively Member
Re: Macro should copy data from One sheet & paste in different format in another shee
one slight correction sir. I will upload the corrected sample excel workbook. Please look into the corrected sample excel workbook
Jan 2nd, 2014, 11:42 AM
#7
Thread Starter
Lively Member
Re: Macro should copy data from One sheet & paste in different format in another shee
Sorry for the confusion. You can use the existing Sample file only.
Jan 2nd, 2014, 11:55 AM
#8
Re: Macro should copy data from One sheet & paste in different format in another shee
So where, for example, does the "10000" in J8 in the Macro Results sheet come from?
Jan 2nd, 2014, 12:01 PM
#9
Thread Starter
Lively Member
Re: Macro should copy data from One sheet & paste in different format in another shee
It comes from cell H6 from "Sample Raw Data" sheet. For each cell in Query results, I have actually placed the references in the column J. So that you can make out from where data is pulling from
Jan 2nd, 2014, 12:19 PM
#10
Re: Macro should copy data from One sheet & paste in different format in another shee
Ok, but if I look at the sample data, the 10000 is not in the "Tirupati TPT" row, it's in the "Madanapalle MPL" row. How would I know it belongs there on the Macro sheet?
Jan 2nd, 2014, 12:23 PM
#11
Re: Macro should copy data from One sheet & paste in different format in another shee
Just saw your post #6, let me know when you've uploaded the corrected version.
Jan 2nd, 2014, 12:25 PM
#12
Thread Starter
Lively Member
Re: Macro should copy data from One sheet & paste in different format in another shee
Oops. Sorry. Just give me one minute.
Jan 2nd, 2014, 12:33 PM
#13
Thread Starter
Lively Member
Re: Macro should copy data from One sheet & paste in different format in another shee
Really Really sorry for the mistake. I have uploaded the corrected sample data. Please use this one and let me know if you have any other questions.
Attached Files
Jan 2nd, 2014, 12:41 PM
#14
Re: Macro should copy data from One sheet & paste in different format in another shee
Jan 2nd, 2014, 12:42 PM
#15
Thread Starter
Lively Member
Re: Macro should copy data from One sheet & paste in different format in another shee
Thank you so much for your cooperation
Jan 2nd, 2014, 01:59 PM
#16
Re: Macro should copy data from One sheet & paste in different format in another shee
See if this gets you close:
Code:
Sub splitData()
Dim wb As Workbook
Dim wsSample As Worksheet
Dim wsMacro As Worksheet
Dim lr As Long
Dim i As Long
Dim j As Integer
Dim wRow As Long
Set wb = ActiveWorkbook
Set wsSample = wb.Worksheets("Sample_Raw_Data")
Set wsMacro = wb.Worksheets("Macro Results")
lr = wsSample.Range("a" & Rows.Count).End(xlUp).Row 'last row of data in column A
Application.WindowState = xlMinimized
Application.ScreenUpdating = False
With wsMacro
For i = 5 To lr
If Not IsEmpty(wsSample.Range("a" & i)) Then
wRow = .Range("a" & Rows.Count).End(xlUp).Row + 1 'WRITE row in Macro sheet
For j = 1 To 7
.Cells(wRow, j) = wsSample.Cells(i, j)
Next j
.Cells(wRow, 8) = "Base Fees"
.Cells(wRow, 9) = "2014"
.Cells(wRow, 10) = wsSample.Cells(i, 8)
.Range("a" & wRow & ":g" & wRow).Copy
.Range("a" & wRow + 1 & ":a" & wRow + 20).PasteSpecial
.Range("h" & wRow + 1 & ":h" & wRow + 5).Value = "Hostel Fees"
For j = 1 To 5
.Cells(wRow + j, 9) = 2014 + j
.Cells(wRow + j, 10) = wsSample.Cells(i, 9 + ((j - 1) * 5))
Next j
.Range("h" & wRow + 6 & ":h" & wRow + 10).Value = "Books"
For j = 1 To 5
.Cells(wRow + 5 + j, 9) = 2014 + j
.Cells(wRow + 5 + j, 10) = wsSample.Cells(i, 10 + ((j - 1) * 5))
Next j
.Range("h" & wRow + 11 & ":h" & wRow + 15).Value = "Dress"
For j = 1 To 5
.Cells(wRow + 10 + j, 9) = 2014 + j
.Cells(wRow + 10 + j, 10) = wsSample.Cells(i, 11 + ((j - 1) * 5))
Next j
.Range("h" & wRow + 16 & ":h" & wRow + 20).Value = "Tuition"
For j = 1 To 5
.Cells(wRow + 15 + j, 9) = 2014 + j
.Cells(wRow + 15 + j, 10) = wsSample.Cells(i, 12 + ((j - 1) * 5))
Next j
End If
Next i
.Range("a1:j1").EntireColumn.AutoFit
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.WindowState = xlNormal
End Sub
Jan 2nd, 2014, 03:54 PM
#17
Thread Starter
Lively Member
Re: Macro should copy data from One sheet & paste in different format in another shee
Thank you so much for this code. I will check and get back to you. You're always the best
Jan 3rd, 2014, 05:43 AM
#18
Thread Starter
Lively Member
Re: Macro should copy data from One sheet & paste in different format in another shee
It's been working exactly as it should. Thank you so much for timely help!!! You have tremendus knowledge in VBA
Jan 3rd, 2014, 07:45 AM
#19
Re: [RESOLVED] Macro should copy data from One sheet & paste in different format in a
Posting Permissions
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
Forum Rules
Click Here to Expand Forum to Full Width