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

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

Perhab

Please Login or Register  to view this content.