Is there any way for me to have my macro copy and paste data from another sheet onto a master sheet while retaining the formulas on the master sheet? So essentially I want my macro to paste the text only while retaining the formatting and formulas of the master spreadsheet. I also want to delete the data that I am copying from my daily spreadsheet after I paste it onto the master. Here is my macro so far

Sub macro ()

Workbooks.Open Filename:= _
        ("I:\Master Copy.xlsm"), Password:="nm"

Dim lNewRow As Long
Dim lDataRow As Long

Windows("Master Copy").Activate
ActiveSheet.Unprotect Password = "nm"
    Windows("Daily Worksheet").Activate
    '1. Find last used row in both sheets
    lNewRow = Workbooks("Daily Worksheet").Worksheets("Sheet1").Cells(Worksheets("Sheet1").Rows.Count, "A").End(xlUp).Row
  Windows("Master Copy").Activate
    lDataRow = Workbooks("Master Copy").Worksheets("Submission Log").Cells(Worksheets("Submission Log").Rows.Count, 1).End(xlUp).Row
    lDataRow = lDataRow + 1
    '2. Copy data
    Workbooks("Daily Worksheet").Worksheets("Sheet1").Range("A2:R" & lNewRow).Copy
    '3. Paste data
    Worksheets("Submission Log").Range("A" & lDataRow).PasteSpecial

    Application.CutCopyMode = False

  Windows("Master Copy").Activate

ActiveSheet.Protect Password = "nm"

End Sub
  • If you copy and paste a whole range and that range contains formulas and constants, there is no way that you can only paste the constants and not overwrite the formulas. You will need to copy and paste only the cells with the constants. Change your approach. – teylyn Oct 25 '17 at 21:08
  • I have changed the range, but I still want to keep the formatting of certain columns. I have data validation in columns, am I able to paste and keep the data validation? – Josh W Oct 25 '17 at 21:20
  • You can paste values only, but it will apply to the whole range. If the range contains formulas, then these will be pasted as their values. Data validation and formatting will not be changed if you paste values only. You can also paste only data validation or only formatting, too. Why don't you take a look at the documentation?!! That's what it's there for. – teylyn Oct 26 '17 at 0:02
  • thanks, I ended up paste text only and adding in other code to reformat the pasted text. What is the documentation? – Josh W Oct 26 '17 at 21:24

You can't do what you're asking with a single line of code.

I assume what's in each column is largely compatible between your two sheets (otherwise you wouldn't be trying to copy the entire range from one sheet to another).

Here's one way to do this:

  1. Copy the entire range to the destination sheet as you currently are, but paste text only. This will retain the target sheet's formatting, but overwrite the formulas you want to keep.
  2. On the target sheet, copy the formulas you want to keep from the row above to the newly pasted row. Excel will automatically update any relative references in the formulas. You'll have to do this for each contiguous group of cells that contain formulas.

There's certainly other ways to do this, but all of them will require your VBA code to "know" the references (or how to find them) of the cells that need to be treated differently. There's no built-in feature that performs the several distinct copy types you need to a single range in a single operation. We have VBA because of things like this!

As for deleting the data from the source sheet, I suppose what you're looking for is the .ClearContents method.

  • I ended up doing something similar to this. Thanks – Josh W Oct 26 '17 at 21:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.