Forum Discussion

Bob Hilton's avatar
Bob Hilton
Copper Contributor
Jan 05, 2017

Using VBA Macro to copy data from 1 worksheet to another

I have 10 worksheets in the same format labled S1 thru S10.  I have another worksheet for data entry.  Each row contains data to be copied to one of the other 10 worksheets.  The destination is defined in a cell in that row, e.g. S5.

What is the syntax to paste the data to the desired worsheet?  Any help is greatly appreciated.

Macros & VBA
Need Help
  • Wyn Hopkins's avatar
    Wyn Hopkins
    MVP
    Jan 06, 2017

    Hi Bob

     

    Does the input sheet get cleared out after the data is copied to the other sheets?

      • Wyn Hopkins's avatar
        Wyn Hopkins
        MVP
        Jan 06, 2017

        Hopefully this will get you started in the right direction

         

        There's all sorts of considerations to take into account but this is a basic starting point...

         

         

         

         

        Sub CopyToSheets()
        '

        Dim c As Range
        Dim strDestinationSheet As String

         

         

        For Each c In Range("ColDestination")

           

            strDestinationSheet = c.Value

             c.EntireRow.Copy



             Sheets(strDestinationSheet).Select

              Range("A1").Select

             ActiveSheet.Paste

              Selection.EntireRow.Insert


        Next c


        End Sub

Resources