+ Reply to Thread
Results 1 to 3 of 3

Sub CallMacro to copy data to dest sheet is deleting formula in destination sheet

  1. #1
    Registered User
    Join Date
    10-09-2015
    Location
    CT, USA
    MS-Off Ver
    2013
    Posts
    2

    Sub CallMacro to copy data to dest sheet is deleting formula in destination sheet

    First post on this forum but I have found a lot of helpful info here but haven't found a thread that helps me with this particular problem. I have (with great effort as I am relatively new to codes) written the following code which works but deletes formula on dest sheet:
    Sub CallMacro()
    Range("B1").Sort Key1:=Range("B2"), _
    Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom
    j = 2
    Set destSheet = Worksheets("Mar17")
    Application.EnableEvents = False
    destSheet.Range(destSheet.Cells(2, 1), destSheet.Cells(80, 13)).ClearContents
    For i = 2 To 80
    If Not UCase(Me.Cells(i, 14)) = "X" And IsEmpty(Me.Cells(i, 2).Value) = False Then
    Me.Range(Cells(i, 1), Cells(i, 13)).Copy Destination:=destSheet.Range(destSheet.Cells(j, 1), destSheet.Cells(j, 14))
    j = j + 1
    End If
    Next i
    Application.EnableEvents = True
    End Sub

    Problem that I have been struggling with and I think I am missing something simple- I have a formula in column 7. When I run the code it deletes the formula in all rows that data was not transferred to in the dest sheet. One thing I have tried is SpecialCells(xlCellTypeConstants).ClearContents instead of just ClearContents but get error. My data validation remains in dest sheet. Any insight would be greatly appreciated!

  2. #2
    Valued Forum Contributor Maudibe's Avatar
    Join Date
    12-21-2016
    Location
    USA
    MS-Off Ver
    2010-2013
    Posts
    326

    Re: Sub CallMacro to copy data to dest sheet is deleting formula in destination sheet

    the code it deletes the formula in all rows that data was not transferred to in the dest sheet
    Hi,

    In one instance you are clearing the formulas in column 7 in the range A2:M80
    Please Login or Register  to view this content.
    In a second instance, if conditions met, you would have been overwriting even if they weren't cleared (A2:N80)
    Please Login or Register  to view this content.
    Looking at both code lines, you are clearing cells to row 80 (including formulas) but may not be utilizing all of the 80 rows with cleared cells in column G because of your conditional statement. That is why rows that data was not transferred to have no formula.

    Both ranges involve clear or overwrite rows in column G. I would suggest to either move you formulas to column 15 or paste in an area beyond column 7. If you want to keep the sheet in the current format, I would delete the line that clears the range and add after the conditional statement a code line that erases the row prior to transferring the data to it. This way you are clearing only the rows that will be written to.

    HTH,
    Maud

    Please Login or Register  to view this content.
    Last edited by Maudibe; 02-18-2017 at 12:27 AM.

  3. #3
    Registered User
    Join Date
    10-09-2015
    Location
    CT, USA
    MS-Off Ver
    2013
    Posts
    2

    Re: Sub CallMacro to copy data to dest sheet is deleting formula in destination sheet

    This works. Thank you thank you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA: Looking for a Macro to Match and Copy from Source to Destination Sheet
    By AliGW in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-01-2016, 03:16 AM
  2. Copy rows based on criteria to new sheet, controll the destination range.
    By Petter120 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-04-2014, 02:50 PM
  3. Replies: 4
    Last Post: 08-21-2014, 07:34 PM
  4. VBA formula to copy paste selected data from main sheet to new sheet
    By vbanoob123 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-24-2014, 12:28 PM
  5. Replies: 4
    Last Post: 02-14-2014, 01:56 PM
  6. Replies: 2
    Last Post: 07-25-2013, 07:45 PM
  7. how to copy (source sheet) a sheet going to another sheet (destination)
    By gerard_gonzales33 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-28-2012, 04:54 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1