Copying active cell and adjacent cell to another sheet

mitcheja · Mar 5th 2021

Jobsite Calendar.xlsxI have a question. My boss uses an Excel spreadsheet for a calendar for production jobs at our glass fabrication plant. He has me enter the information into the calendar and also have to put the name of the company and name of the job on a tag that we print out. I would like to automate the process. By this I mean I would like to put the company name and job name in to the calendar and then have it copy them to another sheet which I can then use as a mail merge to make the tags.

The cells colored yellow are the company name and the cell below that the job name. I want to have the code copy all those cells to the other sheet. These cells would have to be transposed because they are in the same column on Sheet1 and I need them in the same row on Sheet2. Is there a way to do this as it is edited- like after you enter the company name and job name?

ozgrid.com/forum/core/index.php?attachment/1230841/

I tried using Range(ActiveCell.Offset(0,0), ActiveCell.Offset(0,1)).Select.Copy to copy the cell I have highlighted and the one below it. I really have no idea how to do this. I'm just trying to minimize the workflow. Please help.

Thanks,

Jim Mitchell

**Carim** · Mar 5th 2021

Hello,

Your life could be much simpler if you always had the same number in between you reference rows highlighted in Yellow ...

Is there a chance to normalize this sheet .... or not ...?

mitcheja · Mar 5th 2021

I wish we could normalize it, but we add jobs on a day based on ability to ship them. By that I mean that we can we may have a lot of smaller jobs (sqft of job is in green cell) or only one or two larger jobs.

Can you choose cells by color?

I don't know anything really about VBA. Any good recommendations on a basic training guide/book?

Jim

**Carim** · Mar 5th 2021

OK ...

Could you have a go ... and test following macro in your file ... ?

Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Union(Range("A:A"), Range("C:C"), Range("E:E"), _
Range("G:G"), Range("I:I"))) Is Nothing Then Exit Sub
If Intersect(Target, Union(Rows("4:4"), Rows("13:13"), Rows("24:24"), _
Rows("33:33"), Rows("44:44"), Rows("53:53"), Rows("62:62"), Rows("73:73"))) Is Nothing Then Exit Sub
Dim x As Long, y As Long
x = Target.Row: y = Target.Column
Dim last As Long
last = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheet2.Cells(last, 1).Value = Sheet1.Cells(x - 1, y).Value
Sheet2.Cells(last, 2).Value = Sheet1.Cells(x, y).Value
End Sub

Display More

Hope this will help

**Carim** · Mar 5th 2021

Sorry ... should have told you how to test your file ...

Each and time you do type in the Job Name ... both the Customer Name and the Job Name get automatically copied to Sheet 2 ...

Since your Sheet 1 is already filled in ... just place your cursor on the different Job Names ... and press F2 then Enter key ...

to simulate a new entry ...

Hope this will help

mitcheja · Mar 5th 2021

Carim,

That is perfect. Does exactly what I need. Now I can enter the information once instead of repeatedly. Can then use sheet2 as mail merge source.

Thank you very much,

Jim

**Carim** · Mar 5th 2021

Quote from mitcheja

Carim,

1. That is perfect. Does exactly what I need.
2. Now I can enter the information once instead of repeatedly.
3. Can then use sheet2 as mail merge source.

Thank you very much,
Jim

Display More

Glad to hear this can help you out

Thanks for your Thanks ...AND for the Like

Regarding your point 2 ... not sure to understand ... please clarify ... wasn't it your objective ?

Regarding your point 3 ... answer is Yes obviously

mitcheja · Mar 5th 2021

The meaning of point 2 is that I used to have to type the information into the calendar and then go into a document and type the same information there to make the tags. I didn't know how I could pull the information from the calendar as a mail merge source and you solved that problem.

Thanks again,

Jim

**Carim** · Mar 5th 2021

Sorry for my misunderstanding ...

Do not know why ... But had the impression it was a complaint ...

Share