Simpify this code

[s45yth]

Basically i am copying a name from cell C4, copying it and pasting it into Cell F4.........

Then if there is a name in Cell C5, Copy it and paste into Cell F4 again...

Then if there is a name in Cell C6, Copy it and paste it into Cell F4 again...

Then if there is no name in Cell C7, stop....

But at a later date, if a name is input into C7, C8, C9 etc.. copy these names into F4 again....

what is happening is when the name is input into Cell F4.... a data table is generated, which is then copied to a new sheet...

Please find a simplified version of this workbook attached and the macro i recorded (upto 5 rows) below..

Any help will be much appreciated....

Thanks

Scotty

Sub Macro1()
'
' Macro1 Macro
'

'
Range("C4").Select
Selection.Copy
Range("F4").Select
ActiveSheet.Paste
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Fees").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "I500001"
Sheets("Fees").Select
Range("H4:L46").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("I500001").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Fees").Select
Range("C5").Select
Application.CutCopyMode = False
Selection.Copy
Range("F4").Select
ActiveSheet.Paste
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Fees").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "I500002"
Sheets("Fees").Select
Range("H4:L46").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("I500002").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Fees").Select
Range("C6").Select
Application.CutCopyMode = False
Selection.Copy
Range("F4").Select
ActiveSheet.Paste
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Fees").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "I500003"
Sheets("Fees").Select
Range("H4:L46").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("I500003").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Fees").Select
Range("C7").Select
Application.CutCopyMode = False
Selection.Copy
Range("F4").Select
ActiveSheet.Paste
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Fees").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet4").Select
Sheets("Sheet4").Name = "I500004"
Sheets("Fees").Select
Range("H4:L46").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("I500004").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Fees").Select
Range("C8").Select
Application.CutCopyMode = False
Selection.Copy
Range("F4").Select
ActiveSheet.Paste
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Fees").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet5").Select
Sheets("Sheet5").Name = "I500005"
Sheets("Fees").Select
Range("H4:L46").Select
Sheets("Fees").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("I500005").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub <!-- / message --><!-- attachments -->

 

[wigi]

To give you first help: to copy a value from 1 cell to another, use:

Range("F4").Value = Range("C4").Value

instead of:

Range("C4").Select
Selection.Copy
Range("F4").Select
ActiveSheet.Paste

This will already simplify it.

Take out all Select's and Activate's.

Look at other posts in the Board for a loop; you need to test how far the data stretch, and stop at the correct cell.