[vba_newbie]

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

[slurpee55]

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.

[vba_newbie]

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 !!

[vba_newbie]

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").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 !!!!!

[vba_newbie]

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 !

[computerman29642]

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.