none
Newly pasted sheet to use reference from the sheet it was copied from RRS feed

  • Question

  • Hi everyone,

    I recorded a macro which will copy the active sheet and past it when the user clicks a button. Let say the macro copies sheet1, create sheet2 and paste the content from sheet1 to sheet2. In sheet1 I have a column "current" and a column "previous" so Sheet2 also has these two columns. Now I want the column "Previous" of sheet2 to equal the "current" column of sheet1. The problem I am facing is that I want this to happen every time the macro from the current sheet runs. so that when the user is done entering the values in the "current" column from sheet2, he clicks on the button of sheet2 that creates Sheet3 with the "previous" column being equal to the "current" column of sheet2.

    and so on. Assume the column range is from A1 to A10 for the "current" and B1 to B10 for the "previous" column

    Any idea on how to do this?

    K.

    Thursday, July 31, 2014 6:04 PM

All replies

  • See if the following does what you want. It copies the worksheet and then on the new worksheet it copies the data from column B to column A and then clears column B.

    The worksheet copy method is the same as right clicking the worksheet tab and then select Move or Copy and copy the worksheet to a new worksheet. to ensure we are on the same wave length the following 2 pictures of the Old and New worksheets.

    Note my comments about renaming the new worksheet. Simply use any string you like for the variable strNewShtName. I simply used "Stats" and concatenated it with the current month abbreviation.

    Old worksheet to be copied.

    New worksheet:

    Code example:

    Sub Macro1()
        Dim wsOld As Worksheet
        Dim wsNew As Worksheet
        Dim strNewShtName As String
        Dim rngToMove As Range
       
        Set wsOld = ActiveSheet
       
        wsOld.Copy After:=wsOld
        Set wsNew = ActiveSheet
       
        '*****************************************************************************
        'Code between the asterisk lines is optional code to rename the new worksheet
        'Create any string you like for the variable strNewShetName
        strNewShtName = "Stats " & Format(Date, "mmm")  'Create new sheet name
       
        'Rename the worksheet. If name exists then errors. Hense the On Error routine and message to user.
        On Error Resume Next
        wsNew.Name = strNewShtName
        If Err.Number <> 0 Then
            MsgBox strNewShtName & " already exists. Sheet not renamed."
        End If
        On Error GoTo 0
        '*****************************************************************************
       
        With wsNew
            Set rngToMove = .Range(.Cells(2, "B"), .Cells(.Rows.Count, "B").End(xlUp))   'Column B data from 2nd row
            rngToMove.Copy Destination:=.Cells(2, "A")     'Destination is column A from the 2nd row
            rngToMove.ClearContents      'Clear data from column B
        End With
    End Sub


    Regards, OssieMac

    Sunday, August 3, 2014 2:08 AM
  • Sorry it took so long to respond but I work on this in my free time only, and I don't hate a lot of free time lately. Thanks for your help, your code does what I needed, I just edited it so that the name of the sheet becomes the value in a certain cell in the new sheet.

    I have an issue with my previous macro though once I enter formulas into a cell of the sheet. The workbook without formulas works perfectly. The one with formulas gives me a compile error saying that the sub or function is undefined:

    Private Sub Worksheet_Calculate()
        Dim strName As String
        strName = ValidName(Range("I4").Value)
        If strName <> Me.Name And strName <> "" Then Me.Name = strName
    End Sub

    Here are the dropbox links to both workbooks if you want to take a look at it and try to help me make the one with the formulas work:

    With formulas: 

    https://www.dropbox.com/s/4tnjts3v16re6k7/D%C3%A9compte%20Template%20With%20Formula.xlsm 

    Without formulas:

    https://www.dropbox.com/s/8p0fohsp5c4p6xl/D%C3%A9compte%20Template%20Without%20Formula.xlsm

     

    Thank you for your time

    K.

    Wednesday, August 6, 2014 12:09 PM
  • ok so I figure out how to make it work by removing the code and just include a Range("I4").Calculate to refresh the cell and make the sheet equal to that particular cell. The only and hopefully last problem is from your code when it does the copy and destination.

    With wsNew
            Set rngToMove = .Range(.Cells(2, "B"), .Cells(.Rows.Count, "B").End(xlUp))   'Column B data from 2nd row
            rngToMove.Copy Destination:=.Cells(2, "A")     'Destination is column A from the 2nd row
            rngToMove.ClearContents      'Clear data from column B
        End With

    I would like to copy and past only the values from the column. Right now I am copy/pasting the formula. How can I edit the code to make it work? I tried this:

    With wsNew
            Set rngToMove = .Range(.Cells(10, "G"), .Cells(25, "G").End(xlUp))   'Column B data from 2nd row
            rngToMove.Copy 'Destination:=.Cells(10, "H")     'Destination is column A from the 2nd row
            .Range("H10").PasteSpecial xlPasteValues
            'rngToMove.ClearContents      'Clear data from column B
        End With

    But it seems to only copy and it does not paste the values to the other column.

    Wednesday, August 6, 2014 3:29 PM
  • Two options for pasting the values only. I normally use Option 1 because it does not leave the pasted area selected (or highlighted) like PasteSpecial does. If the pasted sheet is the active sheet then the line I included to select a single cell overcomes this but sometimes it is not the active sheet and that line does not work.

    Option 1:

        With wsNew
            Set rngToMove = .Range(.Cells(2, "B"), .Cells(.Rows.Count, "B").End(xlUp))
            rngToMove.Copy
            .Cells(2, "A").Resize(rngToMove.Rows.Count, rngToMove.Columns.Count).Value = rngToMove.Value
            Application.CutCopyMode = False
            'rngToMove.ClearContents    'Optional code
        End With

    Option 2:

        With wsNew
            Set rngToMove = .Range(.Cells(2, "B"), .Cells(.Rows.Count, "B").End(xlUp))
            rngToMove.Copy
            .Cells(2, "A").PasteSpecial Paste:=xlPasteValues
            Application.CutCopyMode = False
            .Cells(2, "A").Select   'This line can only be used if the worksheet is the ActiveSheet
            'rngToMove.ClearContents    'Optional code
        End With


    Regards, OssieMac

    Wednesday, August 6, 2014 11:38 PM