How to copy each row from a worksheet into new workbooks?

Worksheet change

• I have a problem with writing a macro which, Everytime that user change the value in cell within a certain range i need to put some values from txtboxes, comboboxes into adjacent cells. What i need and somehow cannot do, is everytime that value in cell will be nothing or deleted i need to revert procedure and clear adjacent cells Sounds easy but i am giving up Please help I have used macro -Private Sub Worksheet_Change(ByVal Target As Range) Dim rHandScan As Range Set rHandScan = ThisWorkbook.ActiveSheet.Range("c7:c26") If Not Intersect(Target, Range("C7:c26")) Is Nothing Then With Target .Offset(0, -1).Value = Sheet1.TextBox1.Text .Offset(0, 1).Value = Sheet1.ComboBox2.Value .Offset(0, 2).Value = Sheet1.ComboBox1.Value End With End If End Sub From reading your area of expertise bio, I believe you are knowledgeable, in the area in which I am lacking. I have tried other Experts and haven¡¯t found anyone who can help I hope you can. The problem I have seems trivial to me but I am unable to resolve my issue. I am fairly new at using VBA inapplications and have been currently working on afile that monitors ongoing projects that originate and ultimately go through a process in our engineering department. This file is designed to project a chart (Chart 1) with details of the projects such as Company Name, Start Date, Target Finish Date, Engineer Assigned and Current Status (where conditional formatting is used to change the background color of the cells in the entire row). Each project is a record in Worksheet 1. Worksheet 1 and Worksheet 2 have identical amount of columns and are formatted the same. Worksheet 2 is designed to be a database for all previous or finished projects. Everything in this Workbook seems to work fine but I would like to make some changes. One of the changes I would like to make is this. In worksheet 1 there is a drop down list box in column Q that has two choices (YES, NO) for when a project is completed. When YES is selected in this column, the code incorporated in this worksheet (Worksheet 1) is designed to target the entire row associated with row cell in column Q then copy all the data within that row and paste it in worksheet 2. This all works well, except when it copies the row in worksheet 1 into the next available row into Worksheet 2, it also copies the conditional formatting (cell shading) that has been incorporated into Worksheet 1. This seems to complicate issues in the database in worksheet 3. Is there something I can incorporate into the code to copy or paste only the data? The entire code is displayed below Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Columns("Q:Q")) Is Nothing Then If Target = "YES" Then Target.EntireRow.Copy Destination:= _ Sheet2.Range("A65536").End(xlUp).Offset(1, 0) Target.EntireRow.SpecialCells(xlCellTypeConstants).ClearContents End If End If End Sub As you can see when the code executes the copy and destination command it returns to the original worksheet1 and executes this line. (Target.EntireRow.SpecialCells(xlCellTypeConstants).ClearContents) This targets the entire row that was just copied and clears the contents in that row without disrupting the original formatting in Worksheet 1. I have experimented with the PasteSpecial command to no avail. There must be a way to copy and paste to a destination without copying the formatting. Your help will be greatly appreciated.

• Answer:

  Private Sub Worksheet_Change(ByVal Target As Range) Dim rHandScan As Range Set rHandScan = ThisWorkbook.ActiveSheet.Range("c7:c26") if Target.count > 1 then exit sub On Error resume Next If Not Intersect(Target, Range("C7:c26")) Is Nothing Then Application.EnableEvents = False if len(trim(Target)) = 0 then ' target is empty, clear values With Target .Offset(0, -1).clearcontents .Offset(0, 1).clearcontents .Offset(0, 2).clearContents End With else ' target is not empty With Target .Offset(0, -1).Value = Sheet1.TextBox1.Text .Offset(0, 1).Value = Sheet1.ComboBox2.Value .Offset(0, 2).Value = Sheet1.ComboBox1.Value End With End If Appication.enableEvents = True End Sub I am surprised no one else could answer the question since this is, as you suspected, a simple copy and paste using paste special. You can't use the syntax you were using when pasting special. Try this: Target.EntireRow.Copy Sheet2.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues