asked on

Excel 2007 VBA Macro

Hello,
I am needing help with a vba macro in excel 2007. I need to come up with a macro in excel that will copy data in one cell on sheet 2 to a "range" of cells in sheet one. The cell to copy from sheet 2 will always be the same, but the data will change as someone enters it. I need this macro to check the cell in the range in sheet1 to make sure it is empty before copying data from sheet 2 to sheet 1. If the cell has data, it needs to move 1 cell down in the same column. Any help is appreciated. Thanks.

Saqib Husain

A single file with a few data is worth much more than a thousand words of explanation. Can you upload a small sample showing what you want?

cyberbard

ASKER

Sure, hope the file helps explain a little better. This will go on a spreadsheet where people enter an amount in sheet 2 to print a receipt, but they want the amount entered to paste over to sheet 1 and make a list of the amounts.
example.xlsx

Ess Kay

http://www.techonthenet.com/excel/macros/copy_range.php

Sub CopyDataToPlan()

    Dim LDate As String
    Dim LColumn As Integer
    Dim LFound As Boolean

    On Error GoTo Err_Execute

    'Retrieve date value to search for
    LDate = Sheets("Rolling Plan").Range("B4").Value

    Sheets("Plan").Select

    'Start at column B
    LColumn = 2
    LFound = False

    While LFound = False

        'Encountered blank cell in row 2, terminate search
        If Len(Cells(2, LColumn)) = 0 Then
            MsgBox "No matching date was found."
            Exit Sub

        'Found match in row 2
        ElseIf Cells(2, LColumn) = LDate Then

            'Select values to copy from "Rolling Plan" sheet
            Sheets("Rolling Plan").Select
            Range("B5:H6").Select
            Selection.Copy

            'Paste onto "Plan" sheet
            Sheets("Plan").Select
            Cells(3, LColumn).Select
            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False

            LFound = True
            MsgBox "The data has been successfully copied."

        'Continue searching
        Else
            LColumn = LColumn + 1
        End If

    Wend

    On Error GoTo 0

    Exit Sub

Err_Execute:
    MsgBox "An error occurred."

End Sub

Open in new window

hope this helps

Ess Kay

some more links

http://www.exceltip.com/st/Inserting_a_Copied_Cell(s)_Without_Overwriting_Existing_Data/272.html

http://www.ozgrid.com/forum/showthread.php?t=83247

http://stackoverflow.com/questions/2906416/allow-paste-in-worksheet-without-overwriting-locked-cells

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cyberbard

ASKER