Starting macros

[concreteinterface]

I would like to start screwing around with macros. I've started a book with 2 sheets. If there is a number entered into D2:D21 on Sheet1 it will copy the entire row (A3:K3), move it to Sheet2, paste it, move down one line, go back to Sheet1, check for other rows, and paste them if needed.

Does anyone have a sample of code I can just start experimenting around with?

 

[RalphA]

Creat the code you need, using Tools, Macro, Record new Macro. Then, click on Tools, Macro, Macros..,. choose the macro, and click on Edit to change it. Or, go to a sheet tab, do a right click, choose View code, and edit ihe macro you just rote. If you need more help. post again here.

 

[d0wnt0wn]

in excel 2007 click on view and then macros... then record macro...... do a few copy paste things then stop recording then click on macros....view macros... then click on edit and the code will appear....alternately you can right click on your worksheet and select "view code" on the left window pane select modules and you can look at your code that way...... microsoft will show every single step that you did there and often you do not need some of it but you can always post your macro here and someone can clean it up for you

Hope that helps

 

[d0wnt0wn]

Creat the code you need, using Tools, Macro, Record new Macro. Then, click on Tools, Macro, Macros..,. choose the macro, and click on Edit to change it. Or, go to a sheet tab, do a right click, choose View code, and edit ihe macro you just rote. If you need more help. post again here.

heh you beat me to it ralph

 

[concreteinterface]

Sub Macro1()
'
' Macro1 Macro
'

'
Range("A2:K2").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("A7:K7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("A2").Select
ActiveSheet.Paste
End Sub

OK I've got that part down and I can see what the code is doing and I do understand it (except Application.CutCopyMode = False). Now I need to type in an IF statement to say

IF cell D2 has any value OR if no value move to check E2
Range("A2:K2").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Move down one row to A2
Sheets("Sheet1").Select
Move on to check E2 and repeat

 

[RalphA]

Now, that requires mor knowledge of VBA than Ihaave! Hopefully, someone else will pick this up from here. good luck!

 

[concreteinterface]

bump

 

[Joe Was]

The best way to learn, is to use the boards search utility, and search the board before you post. Play with the code you find, see if between the boards samples and the help in Excel, if you can make some progress and when you really get stuck then post for help showing your code and indicating the one small portion or general idea you have that you just can't get.

Think about the problem and the steps it will take to make it happen. From your post you have not done this yet, and from the recorded code you posted, you are missing steps.

To copy to a sheet you need to have that sheet first, do you want to name a new sheet using an InPutBox to get user input about it or do you want it to be automatically named in some way, using the default name or a value found on the master sheet or by some algorithm. First decide how to start, approach the problem in steps, make progress step by step. Very little ever happens all at once. Decide what needs to happen first then what must happen next, untill the problem is finished.

Sub myNewList()
'Standard module code, like: Module1.
Dim lngLstRow&, lngMyshtsCnt&, lngNewLstRow&
Dim rngMyData As Object
Dim strMyNewShtNm$

'Count the sheets in the Workbook.
lngMyshtsCnt = Worksheets.Count

With Sheets.Add
.Move After:=Sheets(lngMyshtsCnt + 1)
.Name = "myList" & lngMyshtsCnt + 1
End With

strMyNewShtNm = ActiveSheet.Name

'Count the used rows in column D, AKA: 4 on Sheet1.
lngLstRow = Sheets("Sheet1").Cells(Rows.Count, 4).End(xlUp).Row

'Define Sheet1's data range!
Set rngMyData = Sheets("Sheet1").Range("D2:D" & lngLstRow)

'Get starting row on new sheet!
lngNewLstRow = Sheets(strMyNewShtNm).Cells(Rows.Count, 4).End(xlUp).Row + 1

'Get data to move!
For Each Cell In rngMyData

'Test for good data and copy it to the new sheet!
If Cell.Value <> "" Then
lngNewLstRow = lngNewLstRow + 1
Sheets(strMyNewShtNm).Range("A" & lngNewLstRow).Value = Cell.Value
End If

Next Cell
End Sub

 

[concreteinterface]

The best way to learn, is to use the boards search utility, and search the board before you post. Play with the code you find, see if between the boards samples and the help in Excel, if you can make some progress and when you really get stuck then post for help showing your code and indicating the one small portion or general idea you have that you just can't get.

Think about the problem and the steps it will take to make it happen. From your post you have not done this yet, and from the recorded code you posted, you are missing steps.

To copy to a sheet you need to have that sheet first, do you want to name a new sheet using an InPutBox to get user input about it or do you want it to be automatically named in some way, using the default name or usinf a value found on the master sheet or by some algorithm. First decide how to start, approach the problem in steps, make progress step by step. Very little ever happens all at once. Decide what needs to happen first then what must happen next, untill the promlem is finished.

Sub myNewList()
'Standard module code, like: Module1.
Dim lngLstRow&, lngMyshtsCnt&, lngNewLstRow&
Dim rngMyData As Object
Dim strMyNewShtNm$

'Count the sheets in the Workbook.
lngMyshtsCnt = Worksheets.Count

With Sheets.Add
.Move After:=Sheets(lngMyshtsCnt + 1)
.Name = "myList" & lngMyshtsCnt + 1
End With

strMyNewShtNm = ActiveSheet.Name

'Count the used rows in column D, AKA: 4 on Sheet1.
lngLstRow = Sheets("Sheet1").Cells(Rows.Count, 4).End(xlUp).Row

'Define Sheet1's data range!
Set rngMyData = Sheets("Sheet1").Range("D2:D" & lngLstRow)

'Get starting row on new sheet!
lngNewLstRow = Sheets(strMyNewShtNm).Cells(Rows.Count, 4).End(xlUp).Row + 1

'Get data to move!
For Each Cell In rngMyData

'Test fot good data and copy it to the new sheet!
If Cell.Value <> "" Then
lngNewLstRow = lngNewLstRow + 1
Sheets(strMyNewShtNm).Range("A" & lngNewLstRow).Value = Cell.Value
End If

Next Cell
End Sub

I thank you for the help and I will try to understand the code...

I do not agree with the first part of your statement.. I am a Excel beginner not a whiz kid.. All people are different and all people learn differently. When I have a problem I call tech support, have a discussion with people who are savvy on their product, and I learn from them so I don't have to call them the next time I have a similar problem. Searching through a message board for hours on end only to confuse me further is not my thing... Apologies.

 

[Joe Was]

I agree with you and have had to approach the same problem in different ways for different people, you the reasons you stated. This problem in case, you could have solved it in any number of ways and if you look at the other posts, you will find that indeed their are many ways to do this. One of those way may have been along the lines you think. I am merely offering you tools and a structure that can be applied to many problems, which is the real way to learn, trasportability is the key.

The old axiom, you can give a man a fish to eat and he will not be hungry now, or you can teach him how to fish and he will never be hungry, is true to a large extent.

Keep at your code work. Play with the code see what can and can't be done. Programming is learning to speak in a foreign language and in many cases to solve puzzles or mysteries with the information available. The trick is to have a tool set and a system or method, know the steps to take is a big part. If the actual experience proves you have missed as step or need to try a different method, do it, explore that is how you learn to program no matter how you learn other things.