none
How to run this code on same sheet in VBA RRS feed

  • Question

  • Hi All,

    How do I run this macro on same sheet. Currently, every time I run this the data is pasted in new sheet

    Sub copyf()
    '
    ' copyf Macro
    '
    
    '
        Columns("B:H").Select
        Selection.Copy
        Sheets.Add After:=Sheets(Sheets.Count)
        ActiveSheet.Paste
    End Sub
    

    Thanks in advance!

    Thursday, February 5, 2015 12:01 PM

Answers

  • Hi,

    I think i found the required one based on

    http://stackoverflow.com/questions/20805874/excel-vba-copy-and-paste-loop-within-loop

    This is my custom code:

    Dim i As Long
    Dim ii As Long
    Dim LastRow As Long
    Dim wb As Workbook
    Dim sht1 As Worksheet
    Dim sht2 As Worksheet
    
    Set wb = ThisWorkbook
    Set sht1 = wb.Sheets("Sheet1")
    Set sht2 = wb.Sheets("Sheet2")
    
    'Find the last row (in column B) with data.
    LastRow = sht1.Range("B:B").Find("*", SearchDirection:=xlPrevious).Row
    ii = 2
    
    For i = 2 To LastRow
    sht2.Range("A" & ii) = sht1.Range("B" & i).Value
    sht2.Range("B" & ii) = sht1.Range("C" & i).Value
    sht2.Range("C" & ii) = sht1.Range("D" & i).Value
    sht2.Range("D" & ii) = sht1.Range("E" & i).Value
    sht2.Range("E" & ii) = sht1.Range("F" & i).Value
    sht2.Range("F" & ii) = sht1.Range("G" & i).Value
    sht2.Range("G" & ii) = sht1.Range("H" & i).Value
    ii = ii + 1
    Next i
        
    End Sub

    • Marked as answer by Srinidhi Laks Thursday, February 5, 2015 1:53 PM
    Thursday, February 5, 2015 1:53 PM

All replies

  • Hello Srinidhi,

    > every time I run this the data is pasted in new sheet

    That's because you use the ActiveSheet property which returns an object that represents the active sheet (the sheet on top) in the active workbook or in the specified window or workbook. 

    You need to change the macro a bit if you need to paste the data into the same sheet:

    Sub copyf()
    '
    ' copyf Macro
    '
    
    '
        Dim ws as Worksheet
        Set ws = AvtiveSheet
        Columns("B:H").Select
        Selection.Copy
        Sheets.Add After:=Sheets(Sheets.Count)
        ws.Paste
    End Sub
    
    
    
    

    Thursday, February 5, 2015 12:10 PM
  • Hi Eugene,

    Thank you for the reply.

    I'm new to vba. I kinda tried it but its not working. Its not pasting the data and new sheet is formed.

    I have some data from columns B to H in sheet1 and I need to paste them from columns A in sheet2.

     If I make any changes in sheet1 I want to run this code again so that it updates in sheet2.

    Thursday, February 5, 2015 12:26 PM
  • Hi

    Try this one line only.

    ActiveSheet.Columns("B:H").Copy Destination:=Sheet2.Columns("A")

    This will copy in Sheet2 at the same row starting at column A


    Cimjet

    Thursday, February 5, 2015 1:17 PM
  • Hi,

    I think i found the required one based on

    http://stackoverflow.com/questions/20805874/excel-vba-copy-and-paste-loop-within-loop

    This is my custom code:

    Dim i As Long
    Dim ii As Long
    Dim LastRow As Long
    Dim wb As Workbook
    Dim sht1 As Worksheet
    Dim sht2 As Worksheet
    
    Set wb = ThisWorkbook
    Set sht1 = wb.Sheets("Sheet1")
    Set sht2 = wb.Sheets("Sheet2")
    
    'Find the last row (in column B) with data.
    LastRow = sht1.Range("B:B").Find("*", SearchDirection:=xlPrevious).Row
    ii = 2
    
    For i = 2 To LastRow
    sht2.Range("A" & ii) = sht1.Range("B" & i).Value
    sht2.Range("B" & ii) = sht1.Range("C" & i).Value
    sht2.Range("C" & ii) = sht1.Range("D" & i).Value
    sht2.Range("D" & ii) = sht1.Range("E" & i).Value
    sht2.Range("E" & ii) = sht1.Range("F" & i).Value
    sht2.Range("F" & ii) = sht1.Range("G" & i).Value
    sht2.Range("G" & ii) = sht1.Range("H" & i).Value
    ii = ii + 1
    Next i
        
    End Sub

    • Marked as answer by Srinidhi Laks Thursday, February 5, 2015 1:53 PM
    Thursday, February 5, 2015 1:53 PM