Copying certain cells in a row when a text is placed in a column

Sp1d3y

New Member
Joined
Dec 29, 2016
Messages
3
Greetings, new here and also a newbie to excel but learning my way around. Little background on my excel file (workbook): as of now it consists of 12 sheets all of which are identical for each month and we input information daily tallying certain criteria. There is also a year to date sheet that houses even more information based on the year. We also utilize around 15 different people so some information is based on their names when placed in a cell.
Based on that info I am trying to gather certain info during the month and have it placed on another sheet or have it placed in another excel file.
Example: let's say column F, when a designated "text" is input, I am wanting the information in cell1,cell2,cell14 and cell 15 of that adjacent row to be pasted into another worksheet or excel file. I am not looking to move all the information in the row since we have a ton of it but only looking to copy 4 cells.
I am lost on how to do this or if it's even possible, macros, vbas? Or anything.
Thank you for reading.
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
Hi Sp1d3y, and welcome to the board.

Your request is a fairly standard request. Need to know:

-on what sheet the "column F" is on (name)
-what is the "text" that will input
-where do those four cell values go to, (what sheet name)

Do you want to have the actual "entry of the text" to run the macro? Or do you want to enter "text" and then run the macro from another means, ie. a button?

Howard
 

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
Here is a plain vanilla code using sheet 1, copied to sheet 2, when any text is entered in column F of sheet 1.

The sheet 2 pastes are from columns B to E, next empty cell/row.

Copy this into the sheet 1 module, and it will run for any change in cells of column F.

Howard


Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyCells As Range
    
    If Target.Count > 1 Then Exit Sub
    
    Set MyCells = Range("F:F")
    
Application.ScreenUpdating = False

    If Not Application.Intersect(MyCells, Range(Target.Address)) Is Nothing Then
       
             Target.Offset(, -5).Resize(1, 2).Copy
             Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
             Target.Offset(, 8).Resize(1, 2).Copy
             Sheets("Sheet2").Range("D" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
             
    End If
    
Application.CutCopyMode = True
Application.ScreenUpdating = True
End Sub
 

Sp1d3y

New Member
Joined
Dec 29, 2016
Messages
3
Howard, glad one of us sees this as a simple request and thank you for your response.
So in the (trying to get the lingo correct) main file [performance indicators] we have work sheets Jan through Dec since we want the info to continue to collect through out the year. I would like the Macro to be automatic when the certain text is placed in the cell (this would be modified for each of the 6 people).
Let's say in Jan the name would be input in column CQ (42:150) and when it does I would like info from that joining row (cells A42, C42, CU42, CV42) to be input in another file [FHNVG Log] in sheet "same text as input on the previous file" starting with A6 through D6 respectively. Reason is ever time that name is found to be entered it would move the designated info and keep a scrolling list of each occurrence. I hope I made it more clear. I can try uploading pics if needed. I'm just not sure the easiest and correct way to have this happen or if it's possible.
 

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514

ADVERTISEMENT

Howard, glad one of us sees this as a simple request and thank you for your response.
So in the (trying to get the lingo correct) main file [performance indicators] we have work sheets Jan through Dec since we want the info to continue to collect through out the year. I would like the Macro to be automatic when the certain text is placed in the cell (this would be modified for each of the 6 people).
Let's say in Jan the name would be input in column CQ (42:150) and when it does I would like info from that joining row (cells A42, C42, CU42, CV42) to be input in another file [FHNVG Log] in sheet "same text as input on the previous file" starting with A6 through D6 respectively. Reason is ever time that name is found to be entered it would move the designated info and keep a scrolling list of each occurrence. I hope I made it more clear. I can try uploading pics if needed. I'm just not sure the easiest and correct way to have this happen or if it's possible.

The plot thicken a bit. Pics may help somewhat, but what would work better would be to post a link to an exact example workbook. You cannot do attachments here but using one of the link utilities to post a link to a workbook is allowed. I use Drop Box, but there are others.

Include a detailed as possible explanation of what you want to happen and where, referring to specific sheets, columns, cells and such.

Howard
 

Sp1d3y

New Member
Joined
Dec 29, 2016
Messages
3
I figured it would put a spin on things. I appreciate the help. Give me a few days to gather it up so I can paint the picture correctly. I can link it to a drop box as that works great on this end too.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,653
Messages
5,626,105
Members
416,161
Latest member
David1966Lewis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top