Copy rows from one ws to another absed on selected criteria

ANDREAAS · Aug 31, 2014

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

, my first time using this site.

Aladin Akyurek · Aug 31, 2014

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

ANDREAAS · Aug 31, 2014

how do i attach my workbook to my post though? Not sure if i am explaining correctly, lets try again

, 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

"

Aladin Akyurek said:
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

DocAElstein · Aug 31, 2014

ANDREAAS said:
........
Also i cannot see how to attach my file to this post , 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.

ANDREAAS · Aug 31, 2014

Thanks Doc
FileSnack | Easy file sharing
See link to my file, hope this simplifies it a bit.

Aladin Akyurek · Aug 31, 2014

ANDREAAS said:
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:


DATE OF TRANSACTION	DRIVER NAME	REGISTRATION NUMBER	KM OUT(BRANCH)
8/8/2014	1	1	1
7/8/2014	2	2	2
4/8/2014	3	3	3
8/8/2014	4	4	4
2/8/2014	5	5	5
7/8/2014	6	6	6
4/8/2014	7	7	7

The desired output in FUEL SUMMARY - MONTHLY...

	July

DATE OF TRANSACTION	DRIVER NAME	REGISTRATION NUMBER	KM OUT(BRANCH)
7/8/2014	2	2	2
7/8/2014	6	6	6

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

snb_ · Aug 31, 2014

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

DocAElstein · Aug 31, 2014

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

A

B

C

D

E

F

G

H

I

2

DATE

DRIVER NAME

REGISTRATION NUMBER

KM OUT(BRANCH)

KM IN(BRANCH)

KM AT FILL UP

LITRES OF FUEL

OIL AMOUNT

FLEET CARD AMOUNT

3

08.08.2014

1

2

3

4

5

6

7

R 1.00

4

08.07.2014

2

4

6

8

10

12

14

R 2.00

5

08.04.2014

3

6

9

12

15

18

21

R 3.00

6

08.08.2014

4

8

12

16

20

24

28

R 4.00

7

08.02.2014

5

10

15

20

25

30

35

R 5.00

FUEL CAPTURE

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

Book1

A

B

C

D

E

F

G

H

I

1

Januar

SELECT MONTH

2

3

DATE

REGISTRATION NUMBER

DRIVER NAME

KM OUT(BRANCH)

KM IN(BRANCH)

KM AT FILL UP

LITRES OF FUEL

OIL AMOUNT

FLEET CARD AMOUNT

4

08.01.2014

16

8

24

32

40

48

R 56.00

R 8.00

FUEL SUMMARY - MONTHLY

. For February this……

Book1

A

B

C

D

E

F

G

H

I

1

Februar

SELECT MONTH

2

3

DATE

REGISTRATION NUMBER

DRIVER NAME

KM OUT(BRANCH)

KM IN(BRANCH)

KM AT FILL UP

LITRES OF FUEL

OIL AMOUNT

FLEET CARD AMOUNT

4

08.02.2014

10

5

15

20

25

30

R 35.00

R 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

DocAElstein · Aug 31, 2014

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

Code again:

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)

ANDREAAS · Sep 1, 2014

Thanks guys!!!!

Awesome stuff.

Copy rows from one ws to another absed on selected criteria

ANDREAAS

New Member

Excel Facts

Aladin Akyurek

MrExcel MVP

ANDREAAS

New Member

DocAElstein

Banned user

ANDREAAS

New Member

Aladin Akyurek

MrExcel MVP

snb_

Well-known Member

DocAElstein

Banned user

DocAElstein

Banned user

ANDREAAS

New Member

Similar threads

Forum statistics

Share this page

Copy rows from one ws to another absed on selected criteria

New Member

Excel Facts

MrExcel MVP

New Member

Banned user

New Member

MrExcel MVP

Well-known Member

Banned user

Banned user

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock