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

macro

Posted by Graham on January 30, 2002 12:55 PM

I have created a macro to copy information from two sheets and paste the information on another sheet. Each time the macro is run I want it to drop down to the next row so the old information is not deleted.

Posted by JohnG on January 30, 2002 1:08 PM

What does the macro look like?

Posted by Larry on January 30, 2002 1:26 PM

***** Here is some basic code that starts at a "starting point", then goes until it finds a blank cell in the worksheet and pastes the data. If your data has blank spaces in it, then this will have problems.

Range("A1").Select
Do Until ActiveCell = ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveSheet.Paste

Posted by Graham on February 01, 2002 4:09 AM


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

Posted by on February 01, 2002 4:23 AM

What was wrong with the answer you got when you posted this previously?