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

dropping down a line

Posted by Graham on January 14, 2002 12:37 PM

Hey
Ive created a maco to collect data from various sheets and paste them onto one sheet. This is a simple maco and runs fine however whenever new data is added to the other sheets and is put onto this one sheet the old data is lost. What I want to do is to after the macro has collected all the information and pasted onto the data sheet I want it to drop down a row so the next time the macro runs it will paste the information of a different row and thus creating a database
This is the macro
Sub booking_log()
'
' booking_log Macro
' Macro recorded 14/01/2002 by GRAHAM OWENS
'

'
Range("C5").Select
Selection.Copy
Sheets("Booking(Log)").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("Conformation").Select
Range("C6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking(Log)").Select
Range("C2").Select
ActiveSheet.Paste
Sheets("Conformation").Select
Range("C7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking(Log)").Select
Range("B2").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").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").Select
ActiveSheet.Paste
Sheets("Conformation").Select
ActiveWindow.ScrollColumn = 2
Range("C14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking(Log)").Select
Range("F2").Select
ActiveSheet.Paste
Sheets("Conformation").Select
Range("C15").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking(Log)").Select
Range("H2").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").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").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").Select
ActiveSheet.Paste
Sheets("Conformation").Select
Range("F10").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking(Log)").Select
Range("N2").Select
ActiveSheet.Paste
Sheets("Conformation").Select
Range("F11").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking(Log)").Select
Range("O2").Select
ActiveSheet.Paste
Sheets("Conformation").Select
Range("F12").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking(Log)").Select
Range("P2").Select
ActiveSheet.Paste
Sheets("Conformation").Select
Range("F13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking(Log)").Select
Range("Q2").Select
ActiveSheet.Paste
Sheets("Conformation").Select
Range("F14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking(Log)").Select
Range("R2").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 Jacob on January 14, 2002 1:44 PM

Hi

When you select the col to paste to use this range("A65536").end(xlup).offset(1,0).select

This will select the next available row, if you want a blank row inbetween then offset(2,0) etc.

HTH

Jacob booking_log Macro Macro recorded 14/01/2002 by GRAHAM OWENS


Posted by Graham on January 16, 2002 8:56 AM


Hey could you show me how to change the code plz im a complete newbie to EXCEL :-(
Thanks in advance booking_log Macro Macro recorded 14/01/2002 by GRAHAM OWENS