Copy Paste Macro

Excel Excel 2007 home and student
February 16, 2010 at 23:43:28
Specs: Windows Vista
Hi guys,

I would love it if someone could help me with this:

I have a form that is made up of two combobox's, a vlookup cell (based on the input from one of the comboboxs) and a couple of other cells that just have regular number data entered into them. What I am attempting to do is have a macro attached to a command button that would copy the data in the cells:

and paste just the data (not any of the codes or formulas) into the "calls" sheet in the same workbook.

I am having issues getting the new data to be copied to the next available line in the calls sheet. I just need the calls sheet to be filled horizontally across the next available row. ANY help at all would be excellent!

I also thought about having a pop up window to let me know when the data has been pasted into the calls sheet, any thoughts?

See More: Copy Paste Macro

February 17, 2010 at 10:32:11

Here is a macro that will copy the cells you specified to the next available row on a worksheet named 'Calls'

I have called the source worksheet 'Source', so either change the name of the source worksheet or change the name in the macro to match what you are using.

To run the macro, I suggest you add a button to your source worksheet
From the Ribbon select Developer (If it's not visible go to the Office Button, select Excel options at the bottom and select the Popular tab and check the 'Show Developer tab in the Ribbon' box)

In Developer - Controls select Insert and choose the button icon.
Draw the button on the worksheet
In the 'Assign Macro' dialog box select 'New'

In the code window that opens enter this:

Option Explicit
Sub Button1_Click()
Dim rngDest As Range

On Error GoTo ErrHnd:

Application.ScreenUpdating = False
'setup the destination range - column A in next empty row
Set rngDest = Worksheets("Calls").Range("A" & CStr(Application.Rows.Count)) _
                .End(xlUp).Offset(1, 0)

With Worksheets("Source")
    'copy and paste as values
    rngDest.PasteSpecial Paste:=xlPasteValues
    rngDest.Offset(0, 1).PasteSpecial Paste:=xlPasteValues
    rngDest.Offset(0, 2).PasteSpecial Paste:=xlPasteValues
    rngDest.Offset(0, 5).PasteSpecial Paste:=xlPasteValues
    rngDest.Offset(0, 6).PasteSpecial Paste:=xlPasteValues
End With
Application.ScreenUpdating = True
Exit Sub

'error handler
Application.ScreenUpdating = True
End Sub

Note that Sub Button1_Click() and End sub will already be present, so don't duplicate them. Option explicit goes before Sub Button1_Click().

Click Save from the Visual Basic Menu.

Alt+f11 takes you back to the main Excel window.

Right click the button and Edit the name to something meaningful

After selecting any cell, the button should now respond to a click.

You can change the code to save different cells, just remember to increase the Offset value if you want to save more cells. You can also change the order that the source cells are saved on the Calls sheet.

Due to the way that the code finds the first empty row on the Calls worksheet, make sure that all cells in column A below the last record are empty.

I haven't added any error checking to this macro or any check to stop duplicates being placed in the Calls worksheet if the button is clicked more than once before the data changes, but checks could be added if this is an issue.


Report •

February 17, 2010 at 21:31:05
You are AMAZING! Thank you so much, it works perfectly! I can't even tell you how much you helped me out, I really really really appreciate it!

Report •

February 18, 2010 at 04:41:31
You're very welcome



Report •
Related Solutions

Ask Question