Creating a macro
 

Hi guys/gals...

I'm NOT at all good with writing macros...in fact, i've never really written
one and i'm trying it out...i have a multiple command macro that i'm trying
to write below...can some one simplify it for me so i can get it to work...i
just want my people to push a button on my spreadsheet..I have everything set
up, but the macro code wont' work...

People will copy data from one sheet and paste it into my "macro" sheet and
then the macro will format it for them...i think it should be pretty simple!!

Thanks in advance!!!
~JC

Cells.Select
Selection.UnMerge
ActiveCell.Offset(0, 5).Columns("A:A").EntireColumn.Select
Selection.Copy
ActiveCell.Offset(0, 3).Columns("A:A").EntireColumn.Select
ActiveSheet.Paste
ActiveCell.Offset(0, -8).Columns("A:H").EntireColumn.Select
ActiveCell.Offset(0, -1).Range("A1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
ActiveCell.Offset(0, -7).Columns("A:A").EntireColumn.Select
ActiveSheet.Range("$A$1:$A$456").RemoveDuplicates Columns:=1, Header:=xlNo
ActiveCell.Rows("1:2").EntireRow.Select
Selection.Delete Shift:=xlUp

Creating a macro
 

Hi JC, what you should do is record your macro and then try to clean it up.
Simply turn on the recorder: Tools, MacroRecord New Macro and begin
manually doing what you want the macro to do. A toolbar will appear with the
Stop Recording button on it. When you have completed doing what you want in
the macro, click that button. You don't have to hurry, if you don't make any
input the recorder is just waiting on you. Test your macro to be sure it is
what you want, then post back to the NG for someone to help clean it up.

The one you posted is too ambiguous to determine exactly what you want to do.

"JC" wrote:

Hi guys/gals...

I'm NOT at all good with writing macros...in fact, i've never really written
one and i'm trying it out...i have a multiple command macro that i'm trying
to write below...can some one simplify it for me so i can get it to work...i
just want my people to push a button on my spreadsheet..I have everything set
up, but the macro code wont' work...

People will copy data from one sheet and paste it into my "macro" sheet and
then the macro will format it for them...i think it should be pretty simple!!

Thanks in advance!!!
~JC

Cells.Select
Selection.UnMerge
ActiveCell.Offset(0, 5).Columns("A:A").EntireColumn.Select
Selection.Copy
ActiveCell.Offset(0, 3).Columns("A:A").EntireColumn.Select
ActiveSheet.Paste
ActiveCell.Offset(0, -8).Columns("A:H").EntireColumn.Select
ActiveCell.Offset(0, -1).Range("A1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
ActiveCell.Offset(0, -7).Columns("A:A").EntireColumn.Select
ActiveSheet.Range("$A$1:$A$456").RemoveDuplicates Columns:=1, Header:=xlNo
ActiveCell.Rows("1:2").EntireRow.Select
Selection.Delete Shift:=xlUp

Creating a macro
 

JLGWhiz,
Thanks for your reply!! Here's the "straight" macro recording..

Cells.Select
Application.CutCopyMode = False
Selection.UnMerge
Rows("1:12").Select
Selection.Delete Shift:=xlUp
Columns("A:E").Select
Range("E1").Activate
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").Select
ActiveSheet.Range("$A$1:$A$456").RemoveDuplicates Columns:=1, Header:=xlNo
Rows("1:2").Select
Range("A2").Activate
Selection.Delete Shift:=xlUp
Range("C4").Select
End Sub

In English here's what I need to do:
1. Select all and unmerge
2. Delete rows 1-12
3. Delete Columns A:E
4. Delete Column B
5. Remove Duplicates
6. Delete Rows 1:2
7. Done.

Thanks again for your help!!
~JC



"JLGWhiz" wrote:

Hi JC, what you should do is record your macro and then try to clean it up.
Simply turn on the recorder: Tools, MacroRecord New Macro and begin
manually doing what you want the macro to do. A toolbar will appear with the
Stop Recording button on it. When you have completed doing what you want in
the macro, click that button. You don't have to hurry, if you don't make any
input the recorder is just waiting on you. Test your macro to be sure it is
what you want, then post back to the NG for someone to help clean it up.

The one you posted is too ambiguous to determine exactly what you want to do.

"JC" wrote:

Hi guys/gals...

I'm NOT at all good with writing macros...in fact, i've never really written
one and i'm trying it out...i have a multiple command macro that i'm trying
to write below...can some one simplify it for me so i can get it to work...i
just want my people to push a button on my spreadsheet..I have everything set
up, but the macro code wont' work...

People will copy data from one sheet and paste it into my "macro" sheet and
then the macro will format it for them...i think it should be pretty simple!!

Thanks in advance!!!
~JC

Cells.Select
Selection.UnMerge
ActiveCell.Offset(0, 5).Columns("A:A").EntireColumn.Select
Selection.Copy
ActiveCell.Offset(0, 3).Columns("A:A").EntireColumn.Select
ActiveSheet.Paste
ActiveCell.Offset(0, -8).Columns("A:H").EntireColumn.Select
ActiveCell.Offset(0, -1).Range("A1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
ActiveCell.Offset(0, -7).Columns("A:A").EntireColumn.Select
ActiveSheet.Range("$A$1:$A$456").RemoveDuplicates Columns:=1, Header:=xlNo
ActiveCell.Rows("1:2").EntireRow.Select
Selection.Delete Shift:=xlUp

Creating a macro
 

This should do what you want. I only compared data in column A
for the duplicates. If your data is not sorted so that duplicate appear
adjacent to each other then the macro fails.

Sub DeleteStuff()
Dim lr As Long
With ActiveSheet
.Rows("1:12").Delete 'Deletes first 12 rows.
Union(.Columns("A:E"), Columns("G")).Delete 'Deletes first 5 col, then
2nd col.
lr = .Cells(.Rows.Count, 1).End(xlUp).Row 'Finds last row with data
in col A
For i = lr To 2 Step -1
If Cells(i, 1) = Cells(i - 1, 1) Then 'Compares data in first cell
of next row up.
Cells(i, 1).EntireRow.Delete 'Deletes if match is found.
End If
Next
.Rows("1:2").Delete 'Deletes first two rows of remaining data.
End With
End Sub

You did not specify if you had header rows to be considered, therefor, the
macro assumes no header rows. If you have them and want to keep them, you
will need to adjust the macro. To find duplicates without sorting the file,
the macro would have to be changed to use the FindNext method.





"JC" wrote:

JLGWhiz,
Thanks for your reply!! Here's the "straight" macro recording..

Cells.Select
Application.CutCopyMode = False
Selection.UnMerge
Rows("1:12").Select
Selection.Delete Shift:=xlUp
Columns("A:E").Select
Range("E1").Activate
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").Select
ActiveSheet.Range("$A$1:$A$456").RemoveDuplicates Columns:=1, Header:=xlNo
Rows("1:2").Select
Range("A2").Activate
Selection.Delete Shift:=xlUp
Range("C4").Select
End Sub

In English here's what I need to do:
1. Select all and unmerge
2. Delete rows 1-12
3. Delete Columns A:E
4. Delete Column B
5. Remove Duplicates
6. Delete Rows 1:2
7. Done.

Thanks again for your help!!
~JC



"JLGWhiz" wrote:

Hi JC, what you should do is record your macro and then try to clean it up.
Simply turn on the recorder: Tools, MacroRecord New Macro and begin
manually doing what you want the macro to do. A toolbar will appear with the
Stop Recording button on it. When you have completed doing what you want in
the macro, click that button. You don't have to hurry, if you don't make any
input the recorder is just waiting on you. Test your macro to be sure it is
what you want, then post back to the NG for someone to help clean it up.

The one you posted is too ambiguous to determine exactly what you want to do.

"JC" wrote:

Hi guys/gals...

I'm NOT at all good with writing macros...in fact, i've never really written
one and i'm trying it out...i have a multiple command macro that i'm trying
to write below...can some one simplify it for me so i can get it to work...i
just want my people to push a button on my spreadsheet..I have everything set
up, but the macro code wont' work...

People will copy data from one sheet and paste it into my "macro" sheet and
then the macro will format it for them...i think it should be pretty simple!!

Thanks in advance!!!
~JC

Cells.Select
Selection.UnMerge
ActiveCell.Offset(0, 5).Columns("A:A").EntireColumn.Select
Selection.Copy
ActiveCell.Offset(0, 3).Columns("A:A").EntireColumn.Select
ActiveSheet.Paste
ActiveCell.Offset(0, -8).Columns("A:H").EntireColumn.Select
ActiveCell.Offset(0, -1).Range("A1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
ActiveCell.Offset(0, -7).Columns("A:A").EntireColumn.Select
ActiveSheet.Range("$A$1:$A$456").RemoveDuplicates Columns:=1, Header:=xlNo
ActiveCell.Rows("1:2").EntireRow.Select
Selection.Delete Shift:=xlUp