Macro to copy data to another worksheet

[brad2157]

I have a workbook with 2 - worksheets "DATA" and "LETTER".

The "DATA" sheet has columns "First Name" "Last Name" "Address"...etc.

The user can enter in either a "X" or a "x" in cells B6:B100 on the "DATA" worksheet. After the user enters a X or x and hits the macro button I need it to copy certain cells from "DATA" worksheet to "LETTER" worksheet. I have listed a example below.

In this example if a "X" or "x" is entered into B6 on "DATA" worksheet and the macro button is hit, then:

C6, D6, & E6 need to be entered into cell C10 in the "LETTER" worksheet

F6 "DATA" to be entered into C11 "LETTER", G6 "DATA" entered into C12 "LETTER"

I6, J6, & K6 "DATA" entered into C17 "LETTER"
____________________________________________________________________
ANOTHER EXAMPLE:

If a "X" or "x" is entered into B7 on "DATA" worksheet and the macro button is hit, then:

C7, D7, & E7 need to be entered into cell C10 in the "LETTER" worksheet

F7 "DATA" to be entered into C11 "LETTER", G7 "DATA" entered into C12 "LETTER"

I7, J7, & K7 "DATA" entered into C17 "LETTER"

____________________________________________________________________
The cells will always be the exact same on the "LETTER" worksheet. Only the "DATA" worksheet will be different cell locations based on where the "X" or "x" is located in column B, but the rows will always correspond (i.e. C6, D6, E6..........C7, D7, E7.......C8, D8, E8..)



I would greatly appreciate any help to get me going on this. I am having a tough time with this one.

 

[jindon]

I think this could be done easily by formula though.....
Paste the code onto Data sheet module

Private Sub Worksheet_Change(ByVal(Target As Range)
With Target
      If Intersect(.Cells, Range("b:b")) Is Nothing Then Exit Sub
      If .Count > 1 Then Exit Sub
      If UCase(.Value) <> "X" Then Exit Sub
      Sheets("Letter").Range("c10").Value= .Offset(,1).Text & .Offset(,2).Text & .Offset(,3).Text
      Sheets("Letter").Range("c11").Value= .Offset(,4).Value
      Sheets("Letter").Range("c12").Value = .Offset(,5).Value
      Sheets("Letter").Range("c17").Value = .Offset(,6).Text & .Offset(,7).Text & .Offset(,8).Text
End With
End Sub

 

[brad2157]

Will this work if I need the sheet to do one row at a time.

For instance..if I have 3 different rows that have a X or x in column B. I am needing it to take the first row that has the X in it, take that data and put into the "LETTER" worksheet, print out only that worksheet......then go back to "DATA" and repeat for the next line that has a X in column B....so on and on....till all columns with a X have been entered into the "LETTER", printed out, and down the entire first sheet "DATA".

 

[brad2157]

I tried the code into my Module for the workbook and I get "Compile Error: Syntax Error" when I try to use it. I only want this to run when the user is done placing the X's and hits the macro button I have at the top of my page.

I tried it again and I get a "Compile error: Expected: Identifier" from the "(" in front of the "Target As Range" portion of the first line of your code. It doesn't like that for some reason.

 

[pfarmer]

I tried the code into my Module for the workbook and I get "Compile Error: Syntax Error" when I try to use it. I only want this to run when the user is done placing the X's and hits the macro button I have at the top of my page.

I tried it again and I get a "Compile error: Expected: Identifier" from the "(" in front of the "Target As Range" portion of the first line of your code. It doesn't like that for some reason.

Two things on the code. One is that This should be in the worksheet event for the individual sheet.

Second is that I think he has it shown incorrectly, it should look like this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Now for a slightly different variation. In this example you have two sheets, one labeled Data, the other Letter.

In the VBA editor select the one marked 'Data' and you should get a blank window. Copy and paste this into the window:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim CopyStatRow As Integer
Dim r As Long
Dim permitstr As String
Dim iColor As Integer
Dim wb As String

On Error GoTo EndMacro

wb = ThisWorkbook.Name

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

permitstr = "x"

On Error Resume Next

iColor = Target.Interior.ColorIndex

If iColor < 0 Then
    iColor = 36
Else
    iColor = iColor + 1
End If

Cells.FormatConditions.Delete

  Application.EnableEvents = False
  r = Cells(Rows.Count, 1).End(xlUp).Row

CopyStatRow = Workbooks(wb).Worksheets("Data").Range("BX1").Value

Range("A" & CopyStatRow).Value = ""

Range("A" & Target.Row).Value = "x"

With Range("A" & Target.Row, "BI" & Target.Row) 'Rows(Target.Row)
    .FormatConditions.Add Type:=2, Formula1:="TRUE"
    .FormatConditions(1).Interior.ColorIndex = iColor
End With

CopyStatRow = Target.Row

Workbooks(wb).Worksheets("Data").Range("BX1").Value = CopyStatRow

EndMacro:

Application.EnableEvents = True

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

Now select the sheet 'Letter' and copy and past this into an empty window:

Private Sub Worksheet_Activate()

Dim TransferRowNumber As Integer
Dim wb As Workbook
Dim ws As Worksheet
Dim wt As Worksheet

On Error GoTo EndMacro

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set wb = ThisWorkbook

Set ws = wb.Worksheets("Data")

Set wt = wb.Worksheets("Letter")

TransferRowNumber = ws.Range("BX1")

wt.Range("B5") = ws.Cells(TransferRowNumber, 2)
wt.Range("C5") = ws.Cells(TransferRowNumber, 3)
wt.Range("D5") = ws.Cells(TransferRowNumber, 4)
wt.Range("E5") = ws.Cells(TransferRowNumber, 5)

EndMacro:

Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

Now whatever line you select on the 'Data' sheet should transfer the contents of columns 'B-E' to columns 'B-E' on row 5 of the 'Letter' sheet when that sheet is selected.

Note the x is not important and is erased when you select another row. If you want to keep track of which ones were selected you could delete this line on the data sheet:

Range("A" & CopyStatRow).Value = ""

You can do variations, for example that would indicate which rows were actually printed. You could have a macro that prints the contents of the sheet 'Letter' and when that is executed you could place an 'o' (which is what I do when I copy the row) in this row. I also do a variation of this as well by remembering the contents of column 'A' and the target.row and then either restore or update that column and target.row based on what action was taken on it.

Another variation which is how I first used this setup was for the second sheet, in your case 'Letter' to use lookups on the first sheet, your sheet 'Data' to pick up the data for the row. vLookups in the various cells would look for the 'x' and then update itself with the columns following the 'x'.

I hope this gives you some ideas on what 'poison' you may want to use in your project and what methods you may want to use for triggering a macro.

Also keep in mind that the value for 'transferrow' which indicates the row that the 'x' is on is stored in a cell located on the 'Data' sheet. You can make this a global variable if desired and get rid of this requirement easily.

If you copy protect the 'Data' sheet remember to allow format changes.

Perry

 

[brad2157]

Hey, this is some really great code. I am going to have to figure out how to modify it a bit though.

What I really want is a "fully automated macro" so to speak, one that I can start...and let run if there are alot of letters to be printed out.

I am doing this to help out my Dad (car salesman). I wanted to give him his own customer database. I also wanted to have the "Data" sheet where he can select the customers he wants to send letters to(Place a X in column B of each row he wants), and then he hits the print all selected button. This would trigger off the whole macro to start with the first X in column B, enter in that Customers data into the "Letter" sheet, send that sheet to the printer, go back to "Data" sheet, find the next row with a X, and carry on...till all X's were found.

Your code is really great and I hope I can retain the features of shading in the entire line selected, etc.. I really like that feature alot.

I noticed that your code places a X on the line you select, is there any way I can just have it look for the X's that the user enters into column B?

I greatly appreciate your help and I know Dad will love this too, it will make it so much easier for him to send out follow up letters to his customers after they purchase vehicles from him.

 

[brad2157]

I figured this out and got this to do exactly what I wanted.

I want to thank everyone who helped and was a part of this. I know Dad will also love to be able to use this. This will save him ALOT of headaches and typing to send out customer letters.

THANKS TO EVERYONE AGAIN!!!