Copy rows from one ws to another absed on selected criteria

ANDREAAS

New Member
Joined
Aug 31, 2014
Messages
4
Hi

I need some help with a formula or VBA to copy entire rows.
If the user selects the month in B1 of FUEL SUMMARY - MONTHLY, the sheet FUEL SUMMARY - MONTHLY must automatically clear and then select all rows from the FUEL CAPTURE sheet with the correct month in column A and copy it to FUEL SUMMARY - MONTHLY.
Also i cannot see how to attach my file to this post :eeek:, my first time using this site.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
how do i attach my workbook to my post though? Not sure if i am explaining correctly, lets try again:confused:, i have in one workbook, two sheets - one called "Fuel Summary - Monthly" and the other "Fuel Capture". in the "Fuel Capture" sheet all data is captured regardless of which month, but column A has the date in yyyy,mm,dd format in both sheets. In the "Fuel Summary - Monthly" sheet i have the same column headers only two rows down. Row B1 has a dropdown list with the months of the year. What i need is when the user selects a month from the dropdown list in the "Fuel Summary Monthly" sheet it goes and searches for all the rows in the "Fuel Capture" sheet where the date matches that month and copies those rows to the Fuel Summary Monthly" sheet which by that time will already be cleared with something like
Code:
Range ("A3:K10000").clearcontents
"
You could adapt from the following set up which uses a formula system:
http://www.mrexcel.com/forum/excel-questions/307832-multiple-vlookups.html#post1511354
 
Upvote 0
........
Also i cannot see how to attach my file to this post :eeek:, my first time using this site.



. There are various ways to do this. The first is preferred by this Forum for excel files as then everyone can see wot is going on quickly.. The Third method I prefer. - Then one can get on straight away with writing a code for you in the file you provide.

. 1 If you can, try uploading this, https://onedrive.live.com/?cid=8cffd...CE27E813%21189 instructions here MrExcel HTML Maker . This free Excel add-In is good for screen shots here of spreadsheets. Then everyone can quickly see what is going on and follow the Thread easily.
Or
. 2 Up left in the Thread editor is a table icon. Click that, create an appropriately sized table and fill it in. (To get this icon up in the Reply window you may need to click on the “Go Advanced” Button next to the Reply Button)
Or
. 3 Supply us with example Excel files (Can of course be shortened, or Made up data in case any info is sensitive)
. For example send over these 2 free things: FileSnack | Easy file sharing or Box Net,
Remember to select Share after uploading and give us the link they provide.

. Try provide clear Tables showing example data but also importantly exactly how the final output should look like in the Excel File based on your actual example data.
 
Upvote 0
Thanks Doc
FileSnack | Easy file sharing
See link to my file, hope this simplifies it a bit.

If I were you, I'd try the set up of the link. Failing that, I'd post a sample from FILE CAPTURE like below:

[TABLE="width: 529"]
<COLGROUP><COL style="WIDTH: 143pt; mso-width-source: userset; mso-width-alt: 6798" width=191><COL style="WIDTH: 119pt; mso-width-source: userset; mso-width-alt: 5660" width=159><COL style="WIDTH: 148pt; mso-width-source: userset; mso-width-alt: 7025" width=198><COL style="WIDTH: 119pt; mso-width-source: userset; mso-width-alt: 5632" width=158><TBODY>[TR]
[TD="class: xl73, width: 191, bgcolor: transparent"] [/TD]
[TD="class: xl73, width: 159, bgcolor: transparent"] [/TD]
[TD="class: xl73, width: 198, bgcolor: transparent"] [/TD]
[TD="class: xl73, width: 158, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl74"]DATE OF TRANSACTION[/TD]
[TD="class: xl74"]DRIVER NAME[/TD]
[TD="class: xl74"]REGISTRATION NUMBER[/TD]
[TD="class: xl74"]KM OUT(BRANCH)[/TD]
[/TR]
[TR]
[TD="class: xl69"]8/8/2014[/TD]
[TD="class: xl70"]1[/TD]
[TD="class: xl70"]1[/TD]
[TD="class: xl70"]1[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]7/8/2014[/TD]
[TD="class: xl70, bgcolor: transparent"]2[/TD]
[TD="class: xl70, bgcolor: transparent"]2[/TD]
[TD="class: xl70, bgcolor: transparent"]2[/TD]
[/TR]
[TR]
[TD="class: xl71"]4/8/2014[/TD]
[TD="class: xl72"]3[/TD]
[TD="class: xl72"]3[/TD]
[TD="class: xl72"]3[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]8/8/2014[/TD]
[TD="class: xl70, bgcolor: transparent"]4[/TD]
[TD="class: xl70, bgcolor: transparent"]4[/TD]
[TD="class: xl70, bgcolor: transparent"]4[/TD]
[/TR]
[TR]
[TD="class: xl69"]2/8/2014[/TD]
[TD="class: xl72"]5[/TD]
[TD="class: xl72"]5[/TD]
[TD="class: xl72"]5[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]7/8/2014[/TD]
[TD="class: xl70, bgcolor: transparent"]6[/TD]
[TD="class: xl70, bgcolor: transparent"]6[/TD]
[TD="class: xl70, bgcolor: transparent"]6[/TD]
[/TR]
[TR]
[TD="class: xl69"]4/8/2014[/TD]
[TD="class: xl72"]7[/TD]
[TD="class: xl72"]7[/TD]
[TD="class: xl72"]7[/TD]
[/TR]
</TBODY>[/TABLE]


The desired output in FUEL SUMMARY - MONTHLY...

[TABLE="width: 513"]
<COLGROUP><COL style="WIDTH: 134pt; mso-width-source: userset; mso-width-alt: 6371" width=179><COL style="WIDTH: 124pt; mso-width-source: userset; mso-width-alt: 5859" width=165><COL style="WIDTH: 133pt; mso-width-source: userset; mso-width-alt: 6314" width=178><COL style="WIDTH: 122pt; mso-width-source: userset; mso-width-alt: 5802" width=163><TBODY>[TR]
[TD="class: xl71, width: 179, bgcolor: transparent"] [/TD]
[TD="class: xl76, width: 165, bgcolor: transparent"]July[/TD]
[TD="class: xl77, width: 178, bgcolor: transparent"] [/TD]
[TD="class: xl71, width: 163, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl75, bgcolor: #3ef00e"]DATE OF TRANSACTION[/TD]
[TD="class: xl75, bgcolor: #3ef00e"]DRIVER NAME[/TD]
[TD="class: xl75, bgcolor: #3ef00e"]REGISTRATION NUMBER[/TD]
[TD="class: xl75, bgcolor: #3ef00e"]KM OUT(BRANCH)[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]7/8/2014[/TD]
[TD="class: xl70, bgcolor: transparent"]2[/TD]
[TD="class: xl70, bgcolor: transparent"]2[/TD]
[TD="class: xl70, bgcolor: transparent"]2[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]7/8/2014[/TD]
[TD="class: xl70, bgcolor: transparent"]6[/TD]
[TD="class: xl70, bgcolor: transparent"]6[/TD]
[TD="class: xl70, bgcolor: transparent"]6[/TD]
[/TR]
</TBODY>[/TABLE]

July is selected from a dropdown; it's actually 7/1/2014, displayed as July.

Note. The above exhibits are directly copiable into Excel. They contain small representative samples of the relevant sheets you have in your workbook. This action makes everything accessible on the board itself, therefore preferred.

All that said:

J3: IDX

J4, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=IFERROR(
  SMALL(
   IF('FUEL CAPTURE'!$A$3:$A$9-DAY('FUEL CAPTURE'!$A$3:$A$9)+1=$B$1,
    ROW('FUEL CAPTURE'!$A$3:$A$9)-ROW('FUEL CAPTURE'!$A$3)+1),
   ROWS($J$4:J4)),"")

A3, just enter, copy across to D3, and down:
Rich (BB code):
=IF($J4="","",INDEX('FUEL CAPTURE'!$A$3:$D$9,$J4,
  MATCH(A$3,'FUEL CAPTURE'!$A$2:$D$2,0)))
 
Upvote 0
In the codemodule of sheet 'Fuel summary monthly'

Code:
private sub worksheet_change(byval target as Range)
  if target.address="$B$1" then 
    cells(2,1).currentregion.clearcontents

    with sheets("Fuel Capture").cells(1).currentregion
       .autofilter 1,target.value
       .offset(1).copy cells(2,1)
       .autofilter
    end with
  end if
End sub
 
Upvote 0
Hi,
. I’d started before the other 2 replied.. so I finished!! Here is my beginner’s attempt!. Surprisingly it seems to work.
. I changed your example data a bit to make it easier for me to check the results. Here is my modified example data: (Note also that I changed your Date heading in Summary to DATE. That is because for my program to work it is important that headings are spelt exactly the same in both sheets)



Book1
ABCDEFGHI
2DATEDRIVER NAMEREGISTRATION NUMBERKM OUT(BRANCH)KM IN(BRANCH)KM AT FILL UPLITRES OF FUELOIL AMOUNTFLEET CARD AMOUNT
308.08.20141234567R 1.00
408.07.20142468101214R 2.00
508.04.201436912151821R 3.00
608.08.2014481216202428R 4.00
708.02.20145101520253035R 5.00
FUEL CAPTURE


. Then, when you select January you get this…..


Book1
ABCDEFGHI
1JanuarSELECT MONTH
2
3DATEREGISTRATION NUMBERDRIVER NAMEKM OUT(BRANCH)KM IN(BRANCH)KM AT FILL UPLITRES OF FUELOIL AMOUNTFLEET CARD AMOUNT
408.01.201416824324048R 56.00R 8.00
FUEL SUMMARY - MONTHLY


. For February this……


Book1
ABCDEFGHI
1FebruarSELECT MONTH
2
3DATEREGISTRATION NUMBERDRIVER NAMEKM OUT(BRANCH)KM IN(BRANCH)KM AT FILL UPLITRES OF FUELOIL AMOUNTFLEET CARD AMOUNT
408.02.201410515202530R 35.00R 5.00
5
FUEL SUMMARY - MONTHLY


….etc. etc. (Do not worry about any German Words- I use Excel German version, but I think it automatically converts it to English when you open it)

. I do not quite understand what is happening with those R’s. I am beginner and know nothing about those strange sort of sorting/ droping - down bits. Maybe you can spread some light on that one for me and explain wot is going on there?


. The size of everything (Rows / columns etc.) is limited to about 255 initially. But that is dead easy to change.

. You can change around the order of, or add new, Headings. The program automatically takes care of that. Just make sure the heading spellings are exactly the same in both sheets

. The Code has lots of unnecessary extra bits and steps and includes lots of annoying green graffiti (comments). But as a beginner I Have to do that to keep track of wot is going on. I will tidy and shorten it a bit and send a more shortened “sane” looking version in a Reply sometime. And anyway, I learn the most when a profi jumps in and gives a better code. So let’s both hope one of them does! On that same note, I would advise to take a good look at wot Aladin Akyurek said and his links etc.. He is a pro and (unlike me) has the experience and knows wot he is doing. For example his approach is probably a lot more “sane” and efficient. Maybe that goes also for the snb_ code, but I did not understand his and could not get it to work.. Probably lack of experience on my behalf.

. So I send the working file with macro in back to you here.
FileSnack | Easy file sharing

. See how you get on and get back if you need more help. I see you already saved your file as .xlsm, so I expect you have a basic idea of running macros (Of course this one starts automatically when you select a new month). You just have to enable macros when asked to when opening the file.


Here is the code: As I said do not be too shocked, I will sanify it a bit sometime!!

[face=Calibri]Option Explicit ' Forces you to define variables-that helps memory space and as a by-product errors show up easier
'----First bit- Sets off main bit once a new date is entered.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo TheEnd ' If anything goes wrong then end Sub without crashing
Dim DateBox As Range 'By doing this you can call all the properties and methods of a Range Object by typing the dot after A1toF1Box
Set DateBox = Range("B1") ' Set that Range to a specific Range
'-------------------------------------
If Intersect(DateBox, Target) Is Nothing Then ' If no intersection in the "Target Area"(that is where you typed or pasted in) and your area of interest(Here the "Box" A1 to F1)...
' - do nothing!!
Else ' The only other possibility is that where you typed or pasted in did intersected with your box. so then:
'######Main Bit-Does wot you want after changing Month---------------
Dim FuelCaptureDate As Date, FuelCaptureMonth As Byte 'Date in date Format, Month as 1.2.3...12
Dim FuelCaptureLastEntryRow As Byte ' Working Out last entry in fuel capture
Let FuelCaptureLastEntryRow = Worksheets("FUEL CAPTURE").Cells.Find(What:="*", _
After:=Worksheets("FUEL CAPTURE").Cells(1, 1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row 'Thanks shg! for this code!
Dim FuelSummaryDate As Date, FuelSummaryMonth As Byte
Let FuelSummaryDate = Worksheets("FUEL SUMMARY - MONTHLY").Range("b1").Value 'Get selscted month-Note must do this as VBA sees it as a date!
Let FuelSummaryMonth = Month(FuelSummaryDate) 'Work out month number from date
Dim FuelSummaryLastEntryRow As Byte ' Working Out last entry, if any, in fuel Summarry
Let FuelSummaryLastEntryRow = Worksheets("FUEL SUMMARY - MONTHLY").Cells.Find(What:="*", _
After:=Worksheets("FUEL SUMMARY - MONTHLY").Cells(1, 1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row 'Thanks shg! for this code!
On Err GoTo TheEnd2 ' Important here , if anything goes wrong go to end2....
Application.EnableEvents = False 'Stop the worksheet change thing while we do the main bit
Worksheets("FUEL SUMMARY - MONTHLY").Range("A4:K" & FuelSummaryLastEntryRow).ClearContents 'Clear any enties allready in Summarry


Dim FuelCaptureRow As Byte, FuelSummeryRow As Byte, LastFuelCaptureColumn As Byte, LastFuelSummaryColumn As Byte 'Limiting everything to 255 for now: can easilly be changed.
Let FuelSummeryRow = 3 'Set initially row in Summary to 3
Let LastFuelCaptureColumn = Worksheets("FUEL CAPTURE").Cells(3, Columns.Count).End(xlToLeft).Column 'Work out actual last column in Fuel capture
Let LastFuelSummaryColumn = 10 'Instead of working out the last column I put in 10 as I see you have something in column AA
Dim FuelSummaryColumn As Byte, FuelCaptureColumn As Byte 'The column count numbers as you go along in next bit

For FuelCaptureRow = 3 To FuelCaptureLastEntryRow ' go thrpugh each row in fuel capture starting at row 3
Let FuelCaptureDate = Worksheets("FUEL CAPTURE").Cells(FuelCaptureRow, 1).Value 'get capture date
Let FuelCaptureMonth = Month(FuelCaptureDate) 'worj out capture minth number 1,2,3.....12
If FuelCaptureMonth = FuelSummaryMonth Then ' Look for match in month, then...
Let FuelSummeryRow = FuelSummeryRow + 1 ' Start new Summarry row
For FuelSummaryColumn = 1 To LastFuelSummaryColumn ' going througth every Fuel Column (Heading)....
For FuelCaptureColumn = 1 To LastFuelCaptureColumn '....go through every fuel capture column (heading)
If Worksheets("FUEL SUMMARY - MONTHLY").Cells(3, FuelSummaryColumn) = Worksheets("FUEL CAPTURE").Cells(2, FuelCaptureColumn).Value Then ' If heading match, then
Worksheets("FUEL SUMMARY - MONTHLY").Cells(FuelSummeryRow, FuelSummaryColumn).Value = Worksheets("FUEL CAPTURE").Cells(FuelCaptureRow, FuelCaptureColumn).Value ' The most important line!!- copy in the appropriate value in Summary
Else
' No heading match so do nothing
End If
Next FuelCaptureColumn ' go on to next captue heading
Next FuelSummaryColumn ' go on to next summary heading
Else
'No Month match so do nothing.
End If
Next FuelCaptureRow ' go oin to next capture Row and start the whole thing again
TheEnd2: 'If anything did go wrong make sure the worksheet change thing is turned back on (to default)
Application.EnableEvents = True 'Turn workksheet change thing back on
End If
'#############End of main Bit---------------------------
'--------------------------
Exit Sub ' End Sub If everything went OK.. Otherwise....


TheEnd: MsgBox "Oh? It didn't work, sorry about that. Alan" ' Appologies from me if it did not work!
End Sub 'Worksheet_Change(ByVal Target As Range)[/face]


Alan_E
 
Upvote 0
Just trying the code again. As always I have problems with different Internat explorers and the MrExcel Editor!!

Code again:

<font face=Calibri><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN> <SPAN style="color:#007F00">' Forces you to define variables-that helps memory space and as a by-product errors show up easier</SPAN><br><SPAN style="color:#007F00">'----First bit- Sets off main bit once a new date is entered.</SPAN><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br><SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> TheEnd <SPAN style="color:#007F00">' If anything goes wrong then end Sub without crashing</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> DateBox <SPAN style="color:#00007F">As</SPAN> Range <SPAN style="color:#007F00">'By doing this you can call all the properties and methods of a Range Object by typing the dot after A1toF1Box</SPAN><br><SPAN style="color:#00007F">Set</SPAN> DateBox = Range("B1") <SPAN style="color:#007F00">' Set that Range to a specific Range</SPAN><br><SPAN style="color:#007F00">'-------------------------------------</SPAN><br>  <SPAN style="color:#00007F">If</SPAN> Intersect(DateBox, Target) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">' If no intersection in the "Target Area"(that is where you typed or pasted in) and your area of interest(Here the "Box" A1 to F1)...</SPAN><br>    <SPAN style="color:#007F00">' - do nothing!!</SPAN><br>  <SPAN style="color:#00007F">Else</SPAN> <SPAN style="color:#007F00">' The only other possibility is that where you typed or pasted in did intersected with your box. so then:</SPAN><br> <SPAN style="color:#007F00">'######Main Bit-Does wot you want after changing Month---------------</SPAN><br> <SPAN style="color:#00007F">Dim</SPAN> FuelCaptureDate <SPAN style="color:#00007F">As</SPAN> Date, FuelCaptureMonth <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">'Date in date Format, Month as 1.2.3...12</SPAN><br> <SPAN style="color:#00007F">Dim</SPAN> FuelCaptureLastEntryRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">' Working Out last entry in fuel capture</SPAN><br> <SPAN style="color:#00007F">Let</SPAN> FuelCaptureLastEntryRow = Worksheets("FUEL CAPTURE").Cells.Find(What:="*", _<br>                              After:=Worksheets("FUEL CAPTURE").Cells(1, 1), _<br>                              Lookat:=xlPart, _<br>                              LookIn:=xlFormulas, _<br>                              SearchOrder:=xlByRows, _<br>                              SearchDirection:=xlPrevious).Row <SPAN style="color:#007F00">'Thanks shg! for this code!</SPAN><br> <SPAN style="color:#00007F">Dim</SPAN> FuelSummaryDate <SPAN style="color:#00007F">As</SPAN> Date, FuelSummaryMonth <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN><br> <SPAN style="color:#00007F">Let</SPAN> FuelSummaryDate = Worksheets("FUEL SUMMARY - MONTHLY").Range("b1").Value <SPAN style="color:#007F00">'Get selscted month-Note must do this as VBA sees it as a date!</SPAN><br> <SPAN style="color:#00007F">Let</SPAN> FuelSummaryMonth = Month(FuelSummaryDate) <SPAN style="color:#007F00">'Work out month number from date</SPAN><br> <SPAN style="color:#00007F">Dim</SPAN> FuelSummaryLastEntryRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">' Working Out last entry, if any, in fuel Summarry</SPAN><br> <SPAN style="color:#00007F">Let</SPAN> FuelSummaryLastEntryRow = Worksheets("FUEL SUMMARY - MONTHLY").Cells.Find(What:="*", _<br>                              After:=Worksheets("FUEL SUMMARY - MONTHLY").Cells(1, 1), _<br>                              Lookat:=xlPart, _<br>                              LookIn:=xlFormulas, _<br>                              SearchOrder:=xlByRows, _<br>                              SearchDirection:=xlPrevious).Row <SPAN style="color:#007F00">'Thanks shg! for this code!</SPAN><br> <SPAN style="color:#00007F">On</SPAN> Err <SPAN style="color:#00007F">GoTo</SPAN> TheEnd2 <SPAN style="color:#007F00">' Important here , if anything goes wrong go to end2....</SPAN><br> Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#007F00">'Stop the worksheet change thing while we do the main bit</SPAN><br> Worksheets("FUEL SUMMARY - MONTHLY").Range("A4:K" & FuelSummaryLastEntryRow).ClearContents <SPAN style="color:#007F00">'Clear any enties allready in Summarry</SPAN><br><br> <SPAN style="color:#00007F">Dim</SPAN> FuelCaptureRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>, FuelSummeryRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>, LastFuelCaptureColumn <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>, LastFuelSummaryColumn <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">'Limiting everything to 255 for now: can easilly be changed.</SPAN><br> <SPAN style="color:#00007F">Let</SPAN> FuelSummeryRow = 3 <SPAN style="color:#007F00">'Set initially row in Summary to 3</SPAN><br> <SPAN style="color:#00007F">Let</SPAN> LastFuelCaptureColumn = Worksheets("FUEL CAPTURE").Cells(3, Columns.Count).End(xlToLeft).Column <SPAN style="color:#007F00">'Work out actual last column in Fuel capture</SPAN><br> <SPAN style="color:#00007F">Let</SPAN> LastFuelSummaryColumn = 10 <SPAN style="color:#007F00">'Instead of working out the last column I put in 10 as I see you have something in column AA</SPAN><br> <SPAN style="color:#00007F">Dim</SPAN> FuelSummaryColumn <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>, FuelCaptureColumn <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">'The column count numbers as you go along in next bit</SPAN><br> <br>   <SPAN style="color:#00007F">For</SPAN> FuelCaptureRow = 3 <SPAN style="color:#00007F">To</SPAN> FuelCaptureLastEntryRow <SPAN style="color:#007F00">' go thrpugh each row in fuel capture starting at row 3</SPAN><br>   <SPAN style="color:#00007F">Let</SPAN> FuelCaptureDate = Worksheets("FUEL CAPTURE").Cells(FuelCaptureRow, 1).Value <SPAN style="color:#007F00">'get capture date</SPAN><br>   <SPAN style="color:#00007F">Let</SPAN> FuelCaptureMonth = Month(FuelCaptureDate) <SPAN style="color:#007F00">'worj out capture minth number 1,2,3.....12</SPAN><br>     <SPAN style="color:#00007F">If</SPAN> FuelCaptureMonth = FuelSummaryMonth <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">' Look for match in month, then...</SPAN><br>       <SPAN style="color:#00007F">Let</SPAN> FuelSummeryRow = FuelSummeryRow + 1 <SPAN style="color:#007F00">' Start new Summarry row</SPAN><br>       <SPAN style="color:#00007F">For</SPAN> FuelSummaryColumn = 1 <SPAN style="color:#00007F">To</SPAN> LastFuelSummaryColumn <SPAN style="color:#007F00">' going througth every Fuel Column (Heading)....</SPAN><br>         <SPAN style="color:#00007F">For</SPAN> FuelCaptureColumn = 1 <SPAN style="color:#00007F">To</SPAN> LastFuelCaptureColumn <SPAN style="color:#007F00">'....go through every fuel capture column (heading)</SPAN><br>           <SPAN style="color:#00007F">If</SPAN> Worksheets("FUEL SUMMARY - MONTHLY").Cells(3, FuelSummaryColumn) = Worksheets("FUEL CAPTURE").Cells(2, FuelCaptureColumn).Value <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">' If heading match, then</SPAN><br>           Worksheets("FUEL SUMMARY - MONTHLY").Cells(FuelSummeryRow, FuelSummaryColumn).Value = Worksheets("FUEL CAPTURE").Cells(FuelCaptureRow, FuelCaptureColumn).Value <SPAN style="color:#007F00">' The most important line!!- copy in the appropriate value in Summary</SPAN><br>           <SPAN style="color:#00007F">Else</SPAN><br>           <SPAN style="color:#007F00">' No heading match so do nothing</SPAN><br>           <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>         <SPAN style="color:#00007F">Next</SPAN> FuelCaptureColumn <SPAN style="color:#007F00">' go on to next captue heading</SPAN><br>       <SPAN style="color:#00007F">Next</SPAN> FuelSummaryColumn <SPAN style="color:#007F00">' go on to next summary  heading</SPAN><br>     <SPAN style="color:#00007F">Else</SPAN><br>      <SPAN style="color:#007F00">'No Month match so do nothing.</SPAN><br>     <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>   <SPAN style="color:#00007F">Next</SPAN> FuelCaptureRow <SPAN style="color:#007F00">' go oin to next capture Row and start the whole thing again</SPAN><br>TheEnd2: <SPAN style="color:#007F00">'If anything did go wrong make sure the worksheet change thing is  turned back on (to default)</SPAN><br>Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#007F00">'Turn workksheet change thing back on</SPAN><br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#007F00">'#############End of main Bit---------------------------</SPAN><br><SPAN style="color:#007F00">'--------------------------</SPAN><br><SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN> <SPAN style="color:#007F00">' End Sub If everything went OK.. Otherwise....</SPAN><br><br>TheEnd:     MsgBox "Oh? It didn't work, sorry about that. Alan" <SPAN style="color:#007F00">' Appologies from me if it did not work!</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN> <SPAN style="color:#007F00">'Worksheet_Change(ByVal Target As Range)</SPAN></FONT>
 
Upvote 0

Forum statistics

Threads
1,226,453
Messages
6,191,134
Members
453,642
Latest member
jefals

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top