MrExcel.com - Your One Stop for Excel Tips & Solutions

Help

Posted by Ryo on January 16, 2002 12:16 PM

hey I created a macro to collect information from different pages and put it into a single page. The system i created is for a booking system and I want to keep all of the information in a single sheet. Everytime the macro runs it deletes the old information to put the new information in. I would like to make the macro drop down a row when it has finished then the next time it runs to paste onto this row and so on.

This is the macro

Range("C5").Select
Selection.Copy
Sheets("Booking(Log)").Select
Range("A2").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Conformation").Select
Range("C6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking(Log)").Select
Range("C2").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Conformation").Select
Range("C7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking(Log)").Select
Range("B2").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Range("D2").Select
Sheets("Conformation").Select
Range("C8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking(Log)").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Conformation").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking(Log)").Select
ActiveSheet.Paste
Sheets("Conformation").Select
Range("C9").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking(Log)").Select
Range("E2").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Conformation").Select
Range("C10").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking(Log)").Select
Range("F2").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
Range("G2").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Conformation").Select
ActiveWindow.ScrollColumn = 2
Range("C14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking(Log)").Select
Range("F2").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Conformation").Select
Range("C15").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking(Log)").Select
Range("H2").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Conformation").Select
Range("C17").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking(Log)").Select
Range("I2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Sheets("Conformation").Select
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
Range("F5").End(xlUp).Offset(1, 0).Select
Selection.Copy
Sheets("Booking(Log)").Select
Range("J2").Select
ActiveSheet.Paste
Sheets("Conformation").Select
Range("F6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking(Log)").Select
Range("K2").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Range("K2").Select
Application.CutCopyMode = False
Selection.Cut Destination:=Range("S2")
Range("S2").Select
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
Range("K2").Select
Sheets("Conformation").Select
Range("F7").Select
Selection.Copy
Sheets("Booking(Log)").Select
ActiveSheet.Paste
Range("L2").Select
Sheets("Conformation").Select
Range("F8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking(Log)").Select
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
Sheets("Conformation").Select
Range("F9").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking(Log)").Select
Range("M2").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Conformation").Select
Range("F10").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking(Log)").Select
Range("N2").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Conformation").Select
Range("F11").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking(Log)").Select
Range("O2").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Conformation").Select
Range("F12").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking(Log)").Select
Range("P2").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Conformation").Select
Range("F13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking(Log)").Select
Range("Q2").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Conformation").Select
Range("F14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking(Log)").Select
Range("R2").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
Range("T2").Select
Sheets("Conformation").Select
Range("F16").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking(Log)").Select
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A3").Select
Sheets("Final").Select
Range("E8").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "You have successfully booked your flights "
With ActiveCell.Characters(Start:=1, Length:=41).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 5
End With
With ActiveCell.Characters(Start:=42, Length:=1).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("E8:J8").Select
Selection.Cut Destination:=Range("D8:I8")
Range("J8").Select
With Selection.Interior
.ColorIndex = 33
.Pattern = xlSolid
End With
Selection.Interior.ColorIndex = 37
Range("H12").Select


End Sub

Thanks in advance

Posted by Antipholus on January 16, 2002 4:34 PM

Check whether the following code (cleaned up a bit) does what you need. By the way, shouldn't the spelling of "Conformation" be Confirmation.

Dim cfm As Worksheet, log As Worksheet, r As Integer
Set cfm = Worksheets("Conformation")
Set log = Worksheets("Booking(Log)")
r = log.Range("A65536").End(xlUp).Offset(1, 0).Row
cfm.Range("C5").Copy log.Range("A" & r)
cfm.Range("C6").Copy log.Range("C" & r)
cfm.Range("C7").Copy log.Range("B" & r)
cfm.Range("C8").Copy log.Range("D" & r)
cfm.Range("C9").Copy log.Range("E" & r)
cfm.Range("C10").Copy log.Range("G" & r)
cfm.Range("C14").Copy log.Range("F" & r)
cfm.Range("C15").Copy log.Range("H" & r)
cfm.Range("C17").Copy
With log.Range("I" & r)
.PasteSpecial Paste:=xlValues
.Font.Bold = True
.Font.ColorIndex = 5
End With
cfm.Range("F5").Copy log.Range("J" & r)
cfm.Range("F6").Copy log.Range("S" & r)
cfm.Range("F7:F14").Copy log.Range("K" & r & ":R" & r)
cfm.Range("F16").Copy log.Range("T" & r)
Application.ScreenUpdating = False
Worksheets("Final").Select
With Range("E8").Characters(Start:=1, Length:=41).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 14
.ColorIndex = 5
End With
With Range("E8").Characters(Start:=42, Length:=1).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.ColorIndex = xlAutomatic
End With
Range("E8").Value = "You have successfully booked your flights "
Range("E8:J8").Cut Destination:=Range("D8:I8")
Range("J8").Interior.ColorIndex = 37
Range("J8").Interior.Pattern = xlSolid
Range("H12").Select