locked
Copy & Paste macro to the next blank column RRS feed

  • Question

  • Hi,

    I need to build the macro to copy column from one workbook to another workbook

    My requirement is to

    1)compare the date column of sheet1 with sheet2(contains only date) and paste the mxt and edsa columns in sheet2

    2)when pasting the mxt and edsa column to the sheet2 there should be two empty columns in between mxt and edsa column

    3)The column c & F values are entered manually ,Column d & g have the percentage value d=b/c% g=e/f%

    4)The updated values of  sheet2  need to copy to sheet3 and when ever i run the macro(updated in sheet1) it needs to copied to next empty column of sheet3

    kindly help me on this

    My input is like this

    Date(A)  MXT(B)  EDSA(C)
    1-Mar    106       86016
    2-Mar     87        86016
    3-Mar     95        86016
    4-Mar     92        86016
    5-Mar     97        86016
    6-Mar     94        86016
    7-Mar
    8-Mar     99        86016
    9-Mar     139      91136

    my output is like this

    Date(A) MXT(B) COL(C) COL(D) EDSA (E)  COL(F)   COL(G)
    1-Mar    106      194       55%     86016    204800   42%
    2-Mar    87        194        45%     86016   204800   42%
    3-Mar    95        194        49%     86016   204800   42%
    4-Mar    92        194        47%     86016   204800   42%
    5-Mar    97        194        50%     86016   204800   42%
    6-Mar    94        194         48%    86016   204800   42%
    7-Mar
    8-Mar    99        194         51%     86016   204800   42%
    9-Mar    139      194          72%    91136   204800   45%

    Tuesday, May 15, 2012 5:39 PM

Answers

  • If they are not dates but are strings, format that column and another column for the date style you want. Then in the other column, use the formula

    =DATEVALUE(A2)

    (assuming your first date is in cell A2) - copy down to match, then copy and paste values over the original data and then delete the column with the formulas.

     


    HTH, Bernie

    Tuesday, May 22, 2012 1:03 PM

All replies

  • My requirement is to

    1)compare the date column of sheet1 with sheet2(contains only date) and paste the mxt and edsa columns in sheet2

    Are you comparing to find similarities, or missing values? Are the dates already entered on Sheet2 or are you appending dates to that sheet, maybe at the bottom of column A?   A before and after view of Sheet 2 is really necessary to determine how the macro would need to work.


    HTH, Bernie

    Tuesday, May 15, 2012 6:19 PM
  • Hi Bernie,

    The date column of sheet2 is already entered( not mxt and edsa) ..now the macro needs  to compare &  copy the  mxt and edsa column in sheet2.

    Wednesday, May 16, 2012 7:32 AM
  • Try this - I have assumed that the sheets are actually named Sheet1 and Sheet2:

    Sub Macro3()
        With Worksheets("Sheet2")
        Intersect(.UsedRange, .Range("B:B")).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
            "=VLOOKUP(RC[-1],'Sheet1'!C[-1]:C,2,FALSE)"
        Intersect(.UsedRange, .Range("E:E")).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
            "=VLOOKUP(RC[-4],'Sheet1'!C[-4]:C[-2],3,FALSE)"
        End With
    End Sub


    HTH, Bernie

    Wednesday, May 16, 2012 1:17 PM
  • Hi Bernie,

    I have one more problem in excel ...when i copied the date from one sheet to another using macro ....some of the dates format is changed ..like my excel contains the date column
    1/3/2012
    2/3/2012
    4/3/2012
    5/3/12
    6/3/12
    7/3/12
    8/3/12
    9/03/12
    10/03/12

    after 4/3/2012.... the dates are changed and it is showing that general fromat cells has no specific number format

    is there any way to convert these to dates  

    Tuesday, May 22, 2012 5:31 AM
  • If they are not dates but are strings, format that column and another column for the date style you want. Then in the other column, use the formula

    =DATEVALUE(A2)

    (assuming your first date is in cell A2) - copy down to match, then copy and paste values over the original data and then delete the column with the formulas.

     


    HTH, Bernie

    Tuesday, May 22, 2012 1:03 PM