Need help with making a repeating Macro

[stewa371]

Sub M()
'
' M Macro
'
' Keyboard Shortcut: Ctrl+s
'
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Range("$A$3:$CL$47683").AutoFilter Field:=1, Criteria1:= _
"280000352896"
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
End Sub

I don't want the macro to take a specific cell, in this case cell A213 which had 280000352896 in it, and only do the action for that cell. I want it to go on to the next cell, do the same ACTION, but on the NEXT cell. I also don't know how to get around the error 1004 I keep getting in this, because its copying from the same worksheet. I tried doing the template work around, but it didn't work. So instead, I just have it save after each completion. I have 16000 rows though, and so that will take way too long even with a macro. All I want it to do is loop the same command, have it copy and paste it onto the second sheet, then go onto the next row until the data runs out. I literally have no idea what I'm doing though, so complete idiot proof explanations would be appreciated. I'm just using the record macro button cause I don't know how to write in VB


This is step by step what I'm trying to do.

I have a column of 16000+ numbers in A, and have columns of info going all the way to AY on each one. What I did before I wrote the Macro was I had Row A2 be a Subtotal line, and A3 I used an auto filter, to get individual numbers from column A and their info in all the other columns across the row.

Now whenever I pick a number from column A for the filter, my A2 row extracts all the necessary info out of the data in the form I need, so all I WANT is to Copy row A2 after its been filtered, and paste the data on a new sheet.

so for example, I need A214's info across the whole row. So I copy the number inside it (280000352904), go to the first sheet in the workbook, go to the auto filter, copy this number into it, and it filters out all entries except that particular number. Now, my A2 Subtotal line has extracted all the info already, so all I want is to copy row A2, go back to sheet 2, and paste that row into row 214. (I do this by just clicking on A214 and pasting. It gets rid of the ID number 280000352896, but I have that elsewhere so I don't care. Then I just want it to do that EXACT same thing, but with 215, and so on and so forth until the end of the data.

www.youtube.com/embed/Xq9_tzcT1BA

This is what I want to do. It's really simple. I just copy the number from the first worksheet, but it in the filter on the second worksheet, it changes the numbers in row A2, due to the formulas I already put in each one of the columns across row A2. Then I just copy row A2 (Just the numbers) and I paste it into the first worksheet. Then I just want to be able to do this all the way down till the end of my data, because there is a crap load of it and I don't wanna have to do this incredibly easy motion 16000 times. But I can't get the macro to work.

Thank you so much

[daffodil11]

Sounds like a For Each macro


Sub ForEach()

Dim c as Range


For Each c in Range("A1:A16000")

'Autofilter stuff
'Paste to other page 1 row below last record

Next c

End Sub

Are you able to attach a sample of your work so we better see what you're working with, and the desired results after the macro?

[stewa371]

No, the info is sensitive, so I can't really post it. I'm basically illiterate when it comes to this stuff. I can see how a for each would work, but I don't know how to write in VB to get this to work. I assume that the "autofilter stuff, paste to other page 1 row below last record" is not actually what needs to be written in the macro, but I'm not sure how to write that.

[daffodil11]

Ok while I start building, give me some range values.

How many columns are in the Filter page? A:M? A:R?

How many rows are in A of the list page?

So far I've watched your video a few times and replicated your macro recording but I just need a few parameters.

Currently working on pasting it back.

Here's the untweaked version, just pulling a list of numbers in A1:A5. c.Row.Paste isn't working out, probably because I have no clue what I'm doing.

Please Login or Register  to view this content.

[daffodil11]

I got this to work. Just matter of updating the ranges.

Please Login or Register  to view this content.

[daffodil11]

Here's my file. Range names are updated with my tab names.
for each autofilter.xlsm

[stewa371]

ok let me check, sorry I was driving home, I'll make a version that doesn't have any sensitive info so that you can see what's up

[stewa371]

I think that might have done it... that saves me a ton of work... I'll have to check it, give me a second

[stewa371]

So the macro worked, and I have the values I need for my data, but the problem now is that for each ID there was multiple instances of that ID. In the original data, this correlated to different times information was gathered on that particular ID, and the corresponding values of each variable. Thanks to the macro, I now have the values I need for the patients, but I have duplicates of them. I tried to just use the remove duplicates for the ID's, but it only gets rid of the ID duplicates, not the entire ROW for the duplicates. Any thoughts?

[stewa371]

Nevermind, I got it, thanks a lot! Solved!