MsOf10 [RESOLVED] Macro should copy data from One sheet & paste in different format in another sheet-VBForums
Results 1 to 19 of 19

Thread: [RESOLVED] Macro should copy data from One sheet & paste in different format in another sheet

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Nov 2013
    Posts
    69

    Resolved [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 Attached Files

  2. #2
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    2,463

    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!

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Nov 2013
    Posts
    69

    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.

  4. #4
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    2,463

    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?

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Nov 2013
    Posts
    69

    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.

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Nov 2013
    Posts
    69

    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

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Nov 2013
    Posts
    69

    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.

  8. #8
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    2,463

    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?

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Nov 2013
    Posts
    69

    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

  10. #10
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    2,463

    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?

  11. #11
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    2,463

    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.

  12. #12

    Thread Starter
    Lively Member
    Join Date
    Nov 2013
    Posts
    69

    Re: Macro should copy data from One sheet & paste in different format in another shee

    Oops. Sorry. Just give me one minute.

  13. #13

    Thread Starter
    Lively Member
    Join Date
    Nov 2013
    Posts
    69

    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 Attached Files

  14. #14
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    2,463

    Re: Macro should copy data from One sheet & paste in different format in another shee

    will do, no problem!

  15. #15

    Thread Starter
    Lively Member
    Join Date
    Nov 2013
    Posts
    69

    Re: Macro should copy data from One sheet & paste in different format in another shee

    Thank you so much for your cooperation

  16. #16
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    2,463

    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

  17. #17

    Thread Starter
    Lively Member
    Join Date
    Nov 2013
    Posts
    69

    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

  18. #18

    Thread Starter
    Lively Member
    Join Date
    Nov 2013
    Posts
    69

    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

  19. #19
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    2,463

    Re: [RESOLVED] Macro should copy data from One sheet & paste in different format in a

    Any time!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.