Avatar of cyberbard
cyberbard
 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.
Microsoft Excel

Avatar of undefined
Last Comment
cyberbard

8/22/2022 - Mon
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
Your help has saved me hundreds of hours of internet surfing.
fblack61
Ess Kay

ASKER CERTIFIED SOLUTION
Saqib Husain

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
cyberbard

ASKER
Alright thank you both, I'll see which one/both works good for me.
cyberbard

ASKER
Thank you for a concise solution!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.