Transferring data using a macro button but skipping out columns containing formulas in the destination

[AdamMc93]

Hello,

I have created a front-end user form designed to transfer data entered, across to a worksheet using a macro button. This works successfully, however a number of the columns in the destination worksheet include formulas. When the data is transferred, the formulas are wiped and the data is pasted as blank text.

However, I require the functionality of being able to amend/update this data in the worksheet, which means that I still require the formulas to exist.

Is there a way I can get the macro to skip columns out so that the columns containing formulas remain untouched?

Below is the VBA code used in the Macro:


Sub UpdateLeadWorksheet()

'cells to copy from Input sheet - some contain formulas
myCopy = "C7:C19,G7:G9,G11:G18,K7:K12,K14:K19"

Set inputWks = Worksheets("Lead Input Form")
Set historyWks = Worksheets("Lead")

With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

With inputWks
Set myRng = .Range(myCopy)
End With

End Sub




I have attempted fixing this by amending the copied cells to skip out the columns I wish to keep the formulas in, but this has not worked. My attempt to fix is below:



Sub UpdateLeadWorksheet()

'cells to copy from Input sheet - some contain formulas
myCopy = "C7:C15,C18:C19,G7:G9,G11:G18,K7:K12,K14:K19"

Set inputWks = Worksheets("Lead Input Form")
Set historyWks = Worksheets("Lead")

With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

With inputWks
Set myRng = .Range(myCopy)
End With

End Sub




Any help / advice would be very much appreciated.

Many thanks in advance.

Adam

 

[Fluff]

Hi & welcome to MrExcel
Neither of those codes is copying anything.

 

[AdamMc93]

Hi,

Thank you for your reply. As you can probably tell i'm new and relatively inexperienced in VBA code

I removed some of the additional code in an attempt to simplify my query, but please find the full code below:

[I][B]Sub UpdateLeadWorksheet()[/B][/I]

[I][B]    'cells to copy from Input sheet - some contain formulas[/B][/I]
[I][B]    myCopy = "C7:C19,G7:G9,G11:G18,K7:K12,K14:K19"[/B][/I]

[I][B]    Set inputWks = Worksheets("Lead Input Form")[/B][/I]
[I][B]    Set historyWks = Worksheets("Lead")[/B][/I]

[I][B]    With historyWks[/B][/I]
[I][B]        nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row[/B][/I]
[I][B]    End With[/B][/I]

[I][B]    With inputWks[/B][/I]
[I][B]        Set myRng = .Range(myCopy)[/B][/I]
[I][B]    End With[/B][/I]

[I][B]    With historyWks[/B][/I]
[I][B]        With .Cells(nextRow, "A")[/B][/I]
[I][B]            .Value = Now[/B][/I]
[I][B]            .NumberFormat = "mm/dd/yyyy hh:mm:ss"[/B][/I]
[I][B]        End With[/B][/I]
[I][B]        .Cells(nextRow, "B").Value = Application.UserName[/B][/I]
[I][B]        oCol = 3[/B][/I]
[I][B]        For Each myCell In myRng.Cells[/B][/I]
[I][B]            historyWks.Cells(nextRow, oCol).Value = myCell.Value[/B][/I]
[I][B]            oCol = oCol + 1[/B][/I]
[I][B]        Next myCell[/B][/I]
[I][B]    End With[/B][/I]

[I][B]    'clear input cells that contain constants[/B][/I]
[I][B]    With inputWks[/B][/I]
[I][B]      On Error Resume Next[/B][/I]
[I][B]         With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)[/B][/I]
[I][B]              .ClearContents[/B][/I]
[I][B]              Application.GoTo .Cells(1) ', Scroll:=True[/B][/I]
[I][B]         End With[/B][/I]
[I][B]      On Error GoTo 0[/B][/I]
[I][B]    End With[/B][/I]
[I][B]End Sub
[/B][/I]

Thanks in advance for any help you may be able to provide.

Adam

 

[Fluff]

Ok, which cells/columns do you want to leave intact & on which sheet are they located?

 

[AdamMc93]

The columns I need to leave intact are on the "Lead" worksheet, and are columns "L", and "M". They both contain V.lookup formulas, which i need to remain to continue using that sheet functionality correctly (the v.lookups will use the data that's transferred in the form)


C16, C17 are the equivalent cells in the "Lead Input Form", which were populating the correct data into the "Lead" worksheet. However obviously the copy and paste function was removing the v.lookup for later amendments.


Hope this helps.


Thank you

 

[Fluff]

Ok try

Sub UpdateLeadWorksheet()

    'cells to copy from Input sheet - some contain formulas
    MyCopy = "[COLOR=#ff0000]C7:C15,C18:C19[/COLOR],G7:G9,G11:G18,K7:K12,K14:K19"

    Set inputWks = Worksheets("Lead Input Form")
    Set historyWks = Worksheets("Lead")

    With historyWks
        NextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
    End With

    With inputWks
        Set myRng = .Range(MyCopy)
    End With

    With historyWks
        With .Cells(NextRow, "A")
            .Value = Now
            .NumberFormat = "mm/dd/yyyy hh:mm:ss"
        End With
        .Cells(NextRow, "B").Value = Application.UserName
        oCol = 3
        For Each MyCell In myRng.Cells
            historyWks.Cells(NextRow, oCol).Value = MyCell.Value
            [COLOR=#ff0000]If oCol = 11 Then oCol = 14 Else oCol = oCol + 1[/COLOR]
        Next MyCell
    End With

    'clear input cells that contain constants
    With inputWks
      On Error Resume Next
         With .Range(MyCopy).Cells.SpecialCells(xlCellTypeConstants)
              .ClearContents
              Application.Goto .Cells(1) ', Scroll:=True
         End With
      On Error GoTo 0
    End With
End Sub

 

[AdamMc93]

Fixed.

Thank you so much for your help

 

[Fluff]

Glad to help & thanks for the feedback