Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Help with macro [Closed]

Report
-
 Yizzal -
hey folks

i am trying to create a macro to copy lines of data to another worksheet baed on the original date meeting certain criteria

I have a sheets called summary, Poker Stars and Party Poker

i want the Summary sheet to copy its data to Poker stars sheet if the data in column D is ="Poker Stars"


if it matchs Poker stars i need it to copy column A to G and also column I, J and L.


data would need to be copied to row 2 onwards as A contains the column headings.

i have tried many ways to make a macro that would update the Poker Stars sheet automatically ,with any new unique records, as soon as i enter data on the summary sheet but cant seem to get it to work, i also tried to get it to just copy unique records when i manually run the macro rather than it being automatic, but cant seem to get that right either :-(
i have also tried adjusting some of the macros listed on the site to fit my criteria.

i am relatively new to excel and have sort of blundered my way through the basic formulas in the sheet but this seems to have got me puzzled even after many you tube instruction videos


Any help greatly appreciated, before i drive myself insane



See more

4 replies

Answer 1 / 4
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 792
if this is one time job no need of a macro
I shall tell you how to do it. try.


1.highlight data in summary sheet
2.click data-filter-autofilter
3. tiny arrows come up inall the columns
4.click the arrow in colun D
5. click "poker stars"
6. you get fillterd data of only poker stars
7.highlight the filtered data and edit copy(or control C)
8.go to poker stars sheet and select A column one row below
if there is no data in poker stars seelct A1
and edit paste(or control V)
12. go back to summary sheet select A1 click data-filter-autofilter
so that autofiler is removed.

if you want a macro you can record macro following steps and edit the macro

if there is problem post back with small extract of summary sheet.
Answer 2 / 4

hey Venkat

thanks alot that was just the info i needed, i have now managed to filter the data , and have managed to record the steps i took as a macro .....prob not the best looking macro , but it seems to work ....ok heres my question if i need to filter this data regularly and only need it to add any new unique records what would i have to add to my macro

.....this macro & filtering certainly getting my brain working :-)

ok heres my very first attempt at using a macro

Sub PokerStars()
'
' PokerStars Macro
'

'
Sheets("Summary").Select
Range("A7:G13").Select
Application.Goto Reference:="R8C1:R1000C16"
ActiveWindow.SmallScroll Down:=-34
Selection.AutoFilter
ActiveSheet.Range("$A$8:$P$120").AutoFilter Field:=4, Criteria1:="<>"
ActiveWindow.SmallScroll Down:=9
ActiveSheet.Range("$A$8:$P$120").AutoFilter Field:=4
ActiveSheet.Range("$A$8:$P$120").AutoFilter Field:=4, Criteria1:= _
"=Poker Stars", Operator:=xlOr, Criteria2:="="
ActiveSheet.Range("$A$8:$P$120").AutoFilter Field:=4, Criteria1:= _
"Poker Stars"
ActiveSheet.Range("$A$8:$P$120").AutoFilter Field:=4, Criteria1:= _
"=Poker Stars", Operator:=xlOr, Criteria2:="="
ActiveSheet.Range("$A$8:$P$120").AutoFilter Field:=4, Criteria1:= _
"Poker Stars"
ActiveWindow.SmallScroll Down:=-17
Range("A8:L661").Select
Selection.Copy
Sheets("Poker stars").Select
ActiveWindow.SmallScroll ToRight:=-4
Range("A8").Select
ActiveSheet.Paste
End Sub
Answer 3 / 4
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 792
You did correctly that you ave RECORDED the macro. but RECORDED macro needs some editing. even by mistake id you went up and down this action also will be RECORDED. you have to remove them. I verbatim copied your RECORDED macro and have given some comments in caps and put a single apostrophe at the beginning of the codes which are not required. I also have given finally how the macro will look without comments. I suggest when you edit recorded macro introduce some message boxes so that when you debug(keeping cursor within the macro and successively hitting F8) you will check whether you are getting what you want.

macro with comments 
Sub PokerStars()
'
' PokerStars Macro
'

'
Sheets("Summary").Select
'Range("A7:G13").Select
'Application.Goto Reference:="R8C1:R1000C16"
'ActiveWindow.SmallScroll Down:=-34
'WHAT IS YOUR MAIN DATA RANGE
'IF THERE ARE NO BLANK ROWS OR COLUMNS TRY THIS
'YOU MAIN DATA SHOULD HAVE COLUMN HEADING FOR AUTOFILERING
'SUPPOSE MAIN DATES STARTS FROM A1.
Dim r As Range 'always put dim statements at the beginning below sub......
Set r = Range("a1").CurrentRegion

'Selection .AutoFilter
'ActiveSheet.Range("$A$8:$P$120").AutoFilter Field:=4, Criteria1:="<>"
'ActiveWindow.SmallScroll Down:=9
'ActiveSheet.Range("$A$8:$P$120").AutoFilter Field:=4
'ActiveSheet.Range("$A$8:$P$120").AutoFilter Field:=4, Criteria1:= _
'"=Poker Stars", Operator:=xlOr, Criteria2:="="
'ActiveSheet.Range("$A$8:$P$120").AutoFilter Field:=4, Criteria1:= _
'"Poker Stars"
'ActiveSheet.Range("$A$8:$P$120").AutoFilter Field:=4, Criteria1:= _
'"=Poker Stars", Operator:=xlOr, Criteria2:="="
'ActiveSheet.Range("$A$8:$P$120").AutoFilter Field:=4, Criteria1:= _
'"Poker Stars"
'ActiveWindow.SmallScroll Down:=-17
r.AutoFilter field:=4, Criteria1:="Poker Stars"

'Range("A8:L661").Select
'Selection.Copy
'INSTED OF THE ABOVE I SHALL GIVE YOU A CODE
r.SpecialCells(xlCellTypeVisible).Copy
'the above copies the filtered data

Sheets("Poker stars").Select
'ActiveWindow.SmallScroll ToRight:=-4
Range("A8").Select
ActiveSheet.Paste
Sheets("Summary").Select
ActiveSheet.AutoFilterMode = False
'THIS REMOVES THE AUTOFILTER AND DATA SAME AS ORIGINAL
End Sub


final macro without comments

I AM ASSUMING THAT THE MAIN DATA IS FROM A1 WITHOUT BLANK ROWS OR COL
you can modify the statement set r=................. 


Sub PokerStars()
Dim r As Range 'always put dim statements at the beginning below sub......
Sheets("Summary").Select
Set r = Range("a1").CurrentRegion
r.AutoFilter field:=4, Criteria1:="Poker Stars"
r.SpecialCells(xlCellTypeVisible).Copy
Sheets("Poker stars").Select
Range("A8").Select
ActiveSheet.Paste
Sheets("Summary").Select
ActiveSheet.AutoFilterMode = False
end sub


if there is any doubt or bug or problem post back.
still more sophistication is possible to reduce the speed of running the macro and that will be after you are more familiar with writing macros.

when you understand the concept then we can think of other criteria other than "poker stars"

good hunting. It is an adventure to learn new things.
Answer 4 / 4

thanks for the code, looking over your version makes so much more sense, i can actually tell what each line of code represents.

Im goin to play around with a few filter and macros and watch a few tutorials , see if i can get the hang of it and try a few more complex ideas.

thanks again for your help i think i understand alot more than i did before i started
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!