Announcement

Collapse
No announcement yet.

VBA Code to Copy cells from one workbook to another when a cell is highlighted.

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA Code to Copy cells from one workbook to another when a cell is highlighted.



    I was wondering what the code would be so that when I color a cell when it was colored say yellow it would copy the entire row to another workbook. I am very new to VBA still. I do not have a sheet to upload currently.

    ~CaptTrout

  • #2
    Re: VBA Code to Copy cells from one workbook to another when a cell is highlighted.

    Easy enough to do if you were to write a macro to do both at the same time... or is there some overriding reason why it has to be when the cell is coloured?

    Comment


    • #3
      Re: VBA Code to Copy cells from one workbook to another when a cell is highlighted.

      It doesn't specifily have to be colored we just thought it would be easier. I have seen true and false statements and i could do a check box or something like that instead.

      Comment


      • #4
        Re: VBA Code to Copy cells from one workbook to another when a cell is highlighted.

        Like I said, what you want to do is easy enough, but how it's done can be as simple or as complex as you want.

        Forget about the 'We thought...' approach. In a wider sense, what are you actually doing? Moving selected rows to a master sheet and marking them on the source sheet as having been copied?

        Comment


        • #5
          Re: VBA Code to Copy cells from one workbook to another when a cell is highlighted.

          this is what i have.

          Code:
          Sub Test()
               'assuming the data is in sheet1
              Sheets("Sheet1").Select
              RowCount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
              For i = 1 To RowCount
                   'assuming the true statment is in column a
                  Range("a" & i).Select
                  check_value = ActiveCell
                  If check_value = "True" Or check_value = "true" Then
                      ActiveCell.EntireRow.Copy
                       'assuming the data is in sheet2
                      Sheets("C:\Users\alec trout\Desktop[book2.xlsm]sheet1").Select
                      RowCount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
                      Range("a" & RowCount + 1).Select
                      ActiveSheet.Paste
                      Sheets("Sheet1").Select
                  End If
              Next
          End Sub
          the bolded part is where i am having issues now. I need to redirect it to the separate excel document to copy the row.
          Last edited by cytop; June 19th, 2012, 16:36.

          Comment


          • #6
            Re: VBA Code to Copy cells from one workbook to another when a cell is highlighted.

            Code:
            Sub Test()
                 'assuming the data is in sheet1
                Sheets("Sheet1").Select
                RowCount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
                
                Dim wb1 As Excel.Workbook
                Dim wb2 As Excel.Workbook
                
                Set wb1 = ActiveWorkbook
                
                '// Change to suit...
                Workbooks.Open Filename:="C:\Temp\xx.xlsx"
                Set wb2 = ActiveWorkbook
                
                wb1.Activate
                
                For i = 1 To RowCount
                     'assuming the true statment is in column a
                    Range("a" & i).Select
                    check_value = ActiveCell
                    
                    If check_value = "True" Or check_value = "true" Then
                        'ActiveCell.EntireRow.Copy
                        
                         'assuming the data is in sheet2
                         
                        wb2.Activate
                        wb2.Sheets("Sheet2").Activate
                        RowCount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
                        Range("a" & RowCount + 1).Select
                        
                        wb1.Sheets("Sheet1").Range("A" & CStr(i)).EntireRow.Copy ActiveCell
                        
                        'ActiveSheet.Paste
                        wb1.Activate
                        
                    End If
                Next
            End Sub
            Not the best code, but it's based on what you had and it works...

            Comment


            • #7
              Re: VBA Code to Copy cells from one workbook to another when a cell is highlighted.

              My true and false statements will be in colum Q or R This is what I have. I changed the location of the file and the Colums.



              Code:
              Sub Test()
                   'assuming the data is in sheet1
                  Sheets("Sheet1").Select
                  RowCount = Cells(Cells.Rows.Count, "q").End(xlUp).Row
                   
                  Dim wb1 As Excel.Workbook
                  Dim wb2 As Excel.Workbook
                   
                  Set wb1 = ActiveWorkbook
                   
                   '// Change to suit...
                  Workbooks.Open Filename:="C:\Users\alec trout\Desktop\HOT PARTS LIST Blank.xlsx"
                  Set wb2 = ActiveWorkbook
                   
                  wb1.Activate
                   
                  For i = 1 To RowCount
                       'assuming the true statment is in column a
                      Range("q" & i).Select
                      check_value = ActiveCell
                       
                      If check_value = "True" Or check_value = "true" Then
                           'ActiveCell.EntireRow.Copy
                           
                           'assuming the data is in sheet2
                           
                          wb2.Activate
                          wb2.Sheets("Sheet2").Activate
                          RowCount = Cells(Cells.Rows.Count, "q").End(xlUp).Row
                          Range("a" & RowCount + 1).Select
                           
                          wb1.Sheets("Sheet1").Range("q" & CStr(i)).EntireRow.Copy ActiveCell
                           
                           'ActiveSheet.Paste
                          wb1.Activate
                           
                      End If
                  Next
              End Sub
              It is telling me "Subscript Out Of Range."

              CaptTrout
              Last edited by CaptTrout; June 20th, 2012, 01:04.

              Comment


              • #8
                Re: VBA Code to Copy cells from one workbook to another when a cell is highlighted.

                Where does the error occur?

                Also, please edit your post and add code tags around your code. These are required by the rules here.

                Click the 'Edit Post' link below the message, highlight the code and click the '#' button on the edit window toolbar.

                Comment


                • #9
                  Re: VBA Code to Copy cells from one workbook to another when a cell is highlighted.

                  I got the code fixed it was an issue with the naming of the sheets now it is giving me a "400" error then when I add in the error catch it tells me label not defined.

                  Update
                  When I run the Macro it takes me to I15 on the sheet i am coping to and tells me "400"

                  Comment


                  • #10
                    Re: VBA Code to Copy cells from one workbook to another when a cell is highlighted.

                    Can you please post a copy of your workbook that throws the error and contains sample data...?

                    Comment


                    • #11
                      Re: VBA Code to Copy cells from one workbook to another when a cell is highlighted.

                      Originally posted by cytop View Post
                      Can you please post a copy of your workbook that throws the error and contains sample data...?
                      Here are the two files. The sample file is the one we are trying to pull data from and the HOT PARTS LIST Blank is the one where we want it to paste into.

                      Sample.xlsxHOT PARTS LIST Blank.xlsx

                      True or False will be in the last column on the sample sheet

                      Comment


                      • #12


                        Re: VBA Code to Copy cells from one workbook to another when a cell is highlighted.

                        This is the final code...Now what i need how to direct it to a specific cell to start inputing the data.

                        Code:
                        Sub Copy()
                         'assuming the data is in sheet1
                            Sheets("Weld Shop").Select
                            RowCount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
                             
                            Dim wb1 As Excel.Workbook
                            Dim wb2 As Excel.Workbook
                             
                            Set wb1 = ActiveWorkbook
                             
                             '// Change to suit...
                            Workbooks.Open Filename:="C:\Users\alec trout\Desktop\HOT PARTS LIST Blank.xlsx"
                            Set wb2 = ActiveWorkbook
                             
                            wb1.Activate
                             
                            For i = 1 To RowCount
                                 'assuming the true statment is in column a
                                Range("q" & i).Select
                                check_value = ActiveCell
                                 
                                If check_value = "True" Or check_value = "true" Then
                                     'ActiveCell.EntireRow.Copy
                                     
                                     'assuming the data is in sheet2
                                     
                                    wb2.Activate
                                    wb2.Sheets("PartsList").Activate
                                    RowCount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
                                    Range("a" & RowCount + 1).Select
                                     
                                    wb1.Sheets("Weld Shop").Range("a" & CStr(i)).EntireRow.Copy ActiveCell
                                     
                                     
                                    wb1.Activate
                                     
                                End If
                            Next
                        End Sub

                        Comment

                        Working...
                        X