Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

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

  1. #1
    Join Date
    19th June 2012
    Posts
    7

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    1st September 2010
    Posts
    7,907

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    19th June 2012
    Posts
    7

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    1st September 2010
    Posts
    7,907

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    19th June 2012
    Posts
    7

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

    this is what i have.

    VB:
    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
                [U][B]            Sheets("C:\Users\alec trout\Desktop[book2.xlsm]sheet1").Select[/B][/U] 
                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 at 16:36.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    1st September 2010
    Posts
    7,907

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

    VB:
     
    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...

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    19th June 2012
    Posts
    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.



    VB:
    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 at 01:04.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    1st September 2010
    Posts
    7,907

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    19th June 2012
    Posts
    7

    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"

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    1st September 2010
    Posts
    7,907

    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...?

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 1
    Last Post: May 10th, 2012, 03:33
  2. Replies: 1
    Last Post: April 21st, 2012, 09:04
  3. Formula/Code For Entering Number In Highlighted Cells
    By Excel_n00b in forum EXCEL HELP
    Replies: 3
    Last Post: November 18th, 2005, 02:30
  4. Copy and paste (an already highlighted) range
    By DarrylP in forum EXCEL HELP
    Replies: 12
    Last Post: October 18th, 2005, 17:44
  5. Sum of highlighted cells
    By popsi in forum EXCEL HELP
    Replies: 2
    Last Post: July 5th, 2005, 09:43

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno