1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Macro that copies whole rows

Discussion in 'Business Applications' started by vba_newbie, Aug 12, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. vba_newbie

    vba_newbie Thread Starter

    Joined:
    Aug 12, 2009
    Messages:
    5
    Hello

    I am a total vba beginer and really need some help. I just can't get my head round the following. Any help would be so very much appreciated.

    This is what I am trying to do-

    I have a sheet with many columns of info. This info is basically a listing of the fruits that Shop A, B and C sell and the different prices and other stats. I want to copy across the rows of info for each shop, sort by fruit but exclude some of the stats.

    example,

    If column 2 equals shop A and column 12 equals pears, then copy the row of info into the new sheet but only the info in columns 2,5,8,12 and 18. I then want a blank row
    Then I want if column 2 equals Shop A and column 12 equals apples, then copy the row of info into the new sheet but only in the info in columns 2,5,8,12 and 18.
    Then I want a blank row
    Then I want if column 2 equals Shop A and column 12 equals peaches, then copy the row of info into the new sheet but only in the info in columns 2,5,8,12 and 18.
    Then a blank row and repeat process for Shop B and C

    If Shop A/B/C has no apples/peaches/pears then nothing will be returned

    After each shop's fruit has been listed I want to total up the prices (column 18 in the orginal sheet)

    And then I want a grand total of Shop A+ shop B +Shop C as the last line.


    I hope I am clear

    Thanks so much in advance (y)
     
  2. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    A), this sounds a lot like homework...
    B), it sounds like an absolutely horrible layout of the data. But, if you are essentially wanting a transposed sorting of the data from columns to rows, I would first get rid of the extra spaces between the stores and move the other data to another worksheet, copy the data going down (for say, apples) and paste special, transpose the data to the row underneath the stores. Then you can add in the columns you took out.
     
  3. vba_newbie

    vba_newbie Thread Starter

    Joined:
    Aug 12, 2009
    Messages:
    5
    Hi Slurpee

    Thanks for your post. I'm not sure I explained myself very clearly... I have come up with a basic marco for this already ( see below), but dont know how to do the following -

    1. I dont want to copy the entire row - I want to exclude some cells. eg, if I am copying row 2 from the sheet, I dont want to include cells d2 and e2 in my new sheet.
    2. I dont know how to put a blank row in between each listing of a shop's fruit.....

    here is what I have so far...... (it gives me the listing I want, but I need blank line in between and also I dont know how to exclude some of the columns from the row that I am copying across)

    Sub Macro1()
    Application.ScreenUpdating = False
    Sheets("Data").Select
    Lastrow = Range("A65536").End(xlUp).Row

    For i = 1 To Lastrow
    Sheets("Data").Select

    If Cells(i, 2) = "SHOP A" _
    And Cells(i, 12) = "APPLES" Then
    Rows(i & ":" & i).Select
    Selection.Copy
    Sheets("Sheet3").Select
    PasteRow = Range("F65536").End(xlUp).Offset(1, 0).Row
    Rows(PasteRow & ":" & PasteRow).Select
    Selection.Insert Shift:=xlDown
    End If

    Next i

    Range("A1").Select
    Application.ScreenUpdating = True
    Application.ScreenUpdating = False
    Sheets("Data").Select
    Lastrow = Range("A65536").End(xlUp).Row

    For i = 1 To Lastrow
    Sheets("Data").Select

    If Cells(i, 2) = "SHOP A" _
    And Cells(i, 12) = "PEARS" Then
    Rows(i & ":" & i).Select
    Selection.Copy
    Sheets("Sheet3").Select
    PasteRow = Range("F65536").End(xlUp).Offset(1, 0).Row
    Rows(PasteRow & ":" & PasteRow).Select
    Selection.Insert Shift:=xlDown
    End If

    Next i

    Range("A1").Select
    Application.ScreenUpdating = True
    End Sub

    Thanks v much in advance !!
     
  4. vba_newbie

    vba_newbie Thread Starter

    Joined:
    Aug 12, 2009
    Messages:
    5
    Hi Slurpie... so I just had a brain wave, and thought maybe it would be easier to take my original spreadsheet and first delete all the irrelevent coloumns. I used the macro recorder to do this and it seems to work (see below). So I thought I could

    1. run the macro to copy the data in 'Data', exlcluding the coloumns I dont want into 'Sheet 1'
    2. then run the second macro that I wrote in the previous post to pull in the data I need from 'Sheet 1' to another sheet, 'Sheet 2'.
    This probably isnt the most efficent way to do things, but hey !

    ... and that leaves me needing to alter my code in step 2. to leave a blank line inbwteen Shop A's Apples, Shop A's Pears etc. I also wanted to total up at the side, the price totals....(see example below)

    SHOP A STAT 1 STAT 2 APPLES 2.30
    SHOP A STAT 1 STAT 2 APPLES 2.30
    4.60
    SHOP A STAT 1 STAT 2 PEARS 3.40
    SHOP A STAT 1 STAT 2 PEARS 4.10
    7.50

    This is the macro recorder code for copying data and deleting some columns -
    Range("A1").Select
    Sheets("Data").Select
    Cells.Select
    Selection.Copy
    Sheets("Sheet1").Select
    ActiveSheet.Paste
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Columns("B:C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("C:D").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:F").Select
    Selection.Delete Shift:=xlToLeft
    ActiveWindow.SmallScroll ToRight:=5
    Columns("G:H").Select
    Selection.Delete Shift:=xlToLeft
    Columns("H:H").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    ActiveWindow.SmallScroll ToRight:=3
    Columns("J:K").Select
    Selection.Delete Shift:=xlToLeft
    Columns("K:M").Select
    Selection.Delete Shift:=xlToLeft
    Columns("L:N").Select
    Selection.Delete Shift:=xlToLeft
    Columns("L:AB").Select
    Selection.Delete Shift:=xlToLeft
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.SmallScroll Down:=9

    This is the code I need to alter for the blank lines and totals -

    Sub Macro1()
    Application.ScreenUpdating = False
    Sheets("Data").Select
    Lastrow = Range("A65536").End(xlUp).Row

    For i = 1 To Lastrow
    Sheets("Data").Select

    If Cells(i, 2) = "SHOP A" _
    And Cells(i, 12) = "APPLES" Then
    Rows(i & ":" & i).Select
    Selection.Copy
    Sheets("Sheet3").Select
    PasteRow = Range("F65536").End(xlUp).Offset(1, 0).Row
    Rows(PasteRow & ":" & PasteRow).Select
    Selection.Insert Shift:=xlDown
    End If

    Next i

    Range("A1").Select
    Application.ScreenUpdating = True
    Application.ScreenUpdating = False
    Sheets("Data").Select
    Lastrow = Range("A65536").End(xlUp).Row

    For i = 1 To Lastrow
    Sheets("Data").Select

    If Cells(i, 2) = "SHOP A" _
    And Cells(i, 12) = "PEARS" Then
    Rows(i & ":" & i).Select
    Selection.Copy
    Sheets("Sheet3").Select
    PasteRow = Range("F65536").End(xlUp).Offset(1, 0).Row
    Rows(PasteRow & ":" & PasteRow).Select
    Selection.Insert Shift:=xlDown
    End If

    Next i

    Range("A1").Select
    Application.ScreenUpdating = True
    End Sub



    THANKS IN ADVANCE !!!!!
     
  5. vba_newbie

    vba_newbie Thread Starter

    Joined:
    Aug 12, 2009
    Messages:
    5
    The totals came out all wrong in my previous post...

    They should have been in the line below, but to the right of the last price
    So if the last price was in D4, the total of all those prices above would show in E5


    Sorry for the confusion !
     
  6. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    I believe the best thing would be to post a sample file (remove any sensitive data with dummy data). Then we all can see exactly what you are seeing and explaining.
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/851670

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice