Changing formats while copying from an open workbook to another

  • Hi Friends,
    Am glad I have this forum to pull me out of my difficulties.
    Here I am having a workbook open from which certain column ranges have to pulled and copied in a new workbook, The below code does exactly as i want.
    The problem comes when I have to upload the new workbook to a website as the formats don't match.


    1) The range M1:M300 contains SALE figures. If the sale figure is ZERO then the cell has to be left BLANK in the destination range F1:F300 and otherwise the other cells which have a sale figure to be copied as it is.




    The rest of the ranges contain DATA which have to be copied in the same format (General) as they are in their respective ranges in the workbook.


    Please Guide.

    Code
    [INDENT]
     'Command Button For "Upload Report" Private Sub CommandButton11_Click() Dim samePath As String Dim wb As Workbook Dim Range1, Range2, Range3, Range4, Range5, Range6 As Range Dim DestRange1, DestRange2, DestRange3, DestRange4, DestRange5, DestRange6 As Range Dim my_Name As String Dim strDate  strDate = ActiveSheet.Name my_Name = InputBox("Enter Date MM-DD-YYYY", "Fravashi Beer and Wine Shop", "SCMSales") samePath = ActiveWorkbook.Path & "\" & my_Name & strDate  If my_Name = " " Then  Exit Sub ElseIf my_Name = "" Then Exit Sub End If  Set Range1 = ActiveSheet.Range("M1:M300") 'Quantity Loose bottles Sold Set Range2 = ActiveSheet.Range("T1:T300") 'Sale date Set Range3 = ActiveSheet.Range("Q1:Q300") 'Local item Code Set Range4 = ActiveSheet.Range("R1:R300") 'Brand name Set Range5 = ActiveSheet.Range("S1:S300") 'Size Set Range6 = ActiveSheet.Range("DA1:DA300") 'Quantity case Sold  With Workbooks.Add Set wb = ActiveWorkbook With wb.ActiveSheet Set DestRange1 = ActiveSheet.Range("F1:F300") Set DestRange2 = ActiveSheet.Range("A1:A300") Set DestRange3 = ActiveSheet.Range("B1:B300") Set DestRange4 = ActiveSheet.Range("C1:C300") Set DestRange5 = ActiveSheet.Range("D1:D300") Set DestRange6 = ActiveSheet.Range("E1:E300") End With End With  Range1.Copy DestRange1.PasteSpecial Paste:=xlPasteValues DestRange1.PasteSpecial Paste:=xlPasteColumnWidths DestRange3.PasteSpecial Paste:=xlPasteFormats Range2.Copy DestRange2.PasteSpecial Paste:=xlPasteValues DestRange2.PasteSpecial Paste:=xlPasteColumnWidths DestRange3.PasteSpecial Paste:=xlPasteFormats Range3.Copy DestRange3.PasteSpecial Paste:=xlPasteValues DestRange3.PasteSpecial Paste:=xlPasteColumnWidths DestRange3.PasteSpecial Paste:=xlPasteFormats Range4.Copy DestRange4.PasteSpecial Paste:=xlPasteValues DestRange4.PasteSpecial Paste:=xlPasteColumnWidths DestRange4.PasteSpecial Paste:=xlPasteFormats Range5.Copy DestRange5.PasteSpecial Paste:=xlPasteValues DestRange5.PasteSpecial Paste:=xlPasteColumnWidths DestRange5.PasteSpecial Paste:=xlPasteFormats Range6.Copy DestRange6.PasteSpecial Paste:=xlPasteValues DestRange6.PasteSpecial Paste:=xlPasteColumnWidths DestRange6.PasteSpecial Paste:=xlPasteFormats  Application.CutCopyMode = False ActiveSheet.Range("G1").Select  wb.SaveAs Filename:=samePath wb.Close Application.ScreenUpdating = True Range("B4").Select End Sub[/INDENT]


    [IMG2=JSON]{"data-align":"none","data-size":"full","src":"https:\/\/www.excelforum.com\/clear.gif"}[/IMG2]Edit Post [IMG2=JSON]{"data-align":"none","data-size":"full","src":"https:\/\/www.excelforum.com\/clear.gif"}[/IMG2]Reply [IMG2=JSON]{"data-align":"none","data-size":"full","src":"https:\/\/www.excelforum.com\/clear.gif"}[/IMG2]Reply With Quote [IMG2=JSON]{"data-align":"none","data-size":"full","src":"https:\/\/www.excelforum.com\/images\/buttons\/multiquote_40b.png"}[/IMG2] Add Reputation Report Post

  • Hi Carim,
    No the object is not to hide zeroes, they are already hidden and the destination worksheet range shows blank, but when you click on a cell the formula bar shows zero, this should not happen because the destination worksheet has to be uploaded to website and the hidden zeroes create a "format not match" problem. so we should have blank cells instead of zeroes in the destination worksheet.
    Please help
    Regards
    Zubin

  • Hi,


    You could test the following


    Code
    Sub Test()
        Cells.Replace What:="0", Replacement:="", LookAt:=xlWhole
    End Sub


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thanks for the file ...


    So you are performing a Copy Paste Values ... from AU1:AU3000 to F1:F3000


    And the objective is to remove all Zero Values in the F column ...


    Is my understanding correct ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Yes correct.........the source worksheet range "AU" has the sale data or the no. of items sold for which certain items can have zero sale values.....These values are copied to destination worksheet. all i need is only that the destination worksheet colum "F" should not show the zero values in the corresponding sale values.
    If you notice with this code the zero values are hidden in the destination folder but when you click on the cell the zero shows up in the formula bar.....this is what is creating problem
    Once again i am thankful to you for devoting your time for me
    Regards
    zubin

  • You could simply change your formula in column AU from


    =AS2+AS3002


    to


    =IF(AS2+AS3002=0,"",AS2+AS3002)

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hi KJBox,
    Thanks for your reply..... I have already tried all this, problem is... there are three more column ranges who's value depend on this column, If i change this to "IF" then there is an error "#Value" in the corresponding column and this has an cascading effect on other columns in the source worksheet.
    so i better thought to change this in the destination worksheet.
    I have uploaded the workbook for your reference.
    Please find a solution
    Regards
    Zubin

  • In that case, this is the code I would use

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hi, Good Morning KJBox.....
    Don't know how to describe you.....Genius nothing less.....not only you solved the problem but your coding works lightening fast
    are there different vba coding languages like other programming languages ???.....every time i get to learn something new....Thanks anyways
    Ok one small request please ....like in the original code can you get back the initial dialog box asking for the name of the destination (New) workbook..actually it is very important, as the system date is pulled up automatically and sometimes the uploading is done on a different date.......
    Rest all is fine and thanks a lot....

  • Here you go, note I changed the new file name to have an underscore between the 2 dates, makes it much more readable.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • In reply to your visitor message, try this

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!