Click on multiple cells from multiple sheets and copy range to new sheet in order

marcosis123

New Member
Joined
Dec 10, 2019
Messages
12
Office Version
365
Platform
MacOS
Hey,

I have very small knowledge of writing vba from scratch, but am ok editing info to suit my needs so any help would be much appreciated.

I’d like to be able to go through an excel document that has multiple sheets and as I’m clicking cells they are copying a range of cells relating to the click and pasting them in a new sheet, one after the other.

so if I select A3 it will copy A3-C3 to a new sheet and then when I select the next one A50 for example it will copy A50-C50 to the next available row on that new sheet.

it may not always be in column A that I select, but it’ll always be the same range of that row. For example column A,B and C of that selected row.

hope that makes sense and thanks again for any help you can give

cheers
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
686
Office Version
365, 2010, 2007
Platform
Windows, Mobile, Web
I would suggest a hotkey to do this, but it's possible with the

Worksheet.SelectionChange event
 

marcosis123

New Member
Joined
Dec 10, 2019
Messages
12
Office Version
365
Platform
MacOS
Thanks for thy
I would suggest a hotkey to do this, but it's possible with the

Worksheet.SelectionChange event
Thanks for the response. How would I go about either of those. As I said I’m ok with figuring out what’s going on with code by looking at it but, as of yet, can’t write it myself.

cheers
 

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
686
Office Version
365, 2010, 2007
Platform
Windows, Mobile, Web
Do you want it limited to a particular sheet? What is the sheet name to copy data to? Can you provide more detail perhaps with sample data?
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
Some users think to copy values to another location they need to select the cell first.
But that is not true.
We can write a script for you that requires no selecting.

For example:

VBA Code:
Sub Copy_Me_Too()
'Modified 1/26/2020 9:27:38 PM  EST
Application.ScreenUpdating = False
Sheets("Alpha").Range("B1").Copy Sheets("Bravo").Range("G14")
Sheets("Alpha").Range("G14").Copy Sheets("Bravo").Range("H45")
Sheets("Bravo").Range("A3").Copy Sheets("Charlie").Range("A45")
Application.ScreenUpdating = True
End Sub
 

marcosis123

New Member
Joined
Dec 10, 2019
Messages
12
Office Version
365
Platform
MacOS
Do you want it limited to a particular sheet? What is the sheet name to copy data to? Can you provide more detail perhaps with sample data?
you can call the sheet whatever you like, test for now.

Theres alot of sheets in the workbook, so I’d just be going through the workbook and selecting random amounts of cells.

the data on each worksheet contains an item code, a product name and a brand name, along with other info that I don’t need.
 

marcosis123

New Member
Joined
Dec 10, 2019
Messages
12
Office Version
365
Platform
MacOS
Some users think to copy values to another location they need to select the cell first.
But that is not true.
We can write a script for you that requires no selecting.

For example:

VBA Code:
Sub Copy_Me_Too()
'Modified 1/26/2020 9:27:38 PM  EST
Application.ScreenUpdating = False
Sheets("Alpha").Range("B1").Copy Sheets("Bravo").Range("G14")
Sheets("Alpha").Range("G14").Copy Sheets("Bravo").Range("H45")
Sheets("Bravo").Range("A3").Copy Sheets("Charlie").Range("A45")
Application.ScreenUpdating = True
End Sub
it’s random cells, different each time throughout the workbook, so unfortunately this wouldn’t work.

thanks though, I’m saving this for something else I’m doing :)
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
Try this:
This script requires you to double click on the cell and then that cell and the two cells to the Right will be copied to a sheet named "Master"

So if you double click on G14
G14 H14 and I14 will be copied to sheet named Master column A B and C
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on any sheet tab
Select View Code from the pop-up context menu

In upper left corner of window double click on ThisWorbook
Paste the code in the VBA edit window
VBA Code:
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Cancel = True
Dim Lastrow As Long
Lastrow = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row + 1
Target.Resize(, 3).Copy Sheets("Master").Cells(Lastrow, 1)
End Sub
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,549
Office Version
2013, 2007
Platform
Windows
VBA Code:
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Selection.Resize(, 3).Copy Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1)
End Sub
 

marcosis123

New Member
Joined
Dec 10, 2019
Messages
12
Office Version
365
Platform
MacOS
Try this:
This script requires you to double click on the cell and then that cell and the two cells to the Right will be copied to a sheet named "Master"

So if you double click on G14
G14 H14 and I14 will be copied to sheet named Master column A B and C
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on any sheet tab
Select View Code from the pop-up context menu

In upper left corner of window double click on ThisWorbook
Paste the code in the VBA edit window
VBA Code:
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Cancel = True
Dim Lastrow As Long
Lastrow = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row + 1
Target.Resize(, 3).Copy Sheets("Master").Cells(Lastrow, 1)
End Sub
That works perfectly. Thanks mate.

My next quest is to somehow transfer these via a macro to a word template to fill in the blanks, but i know this is a different issue, so ill do my research first and see if i can find it myself.

Thanks again
 

Watch MrExcel Video

Forum statistics

Threads
1,102,907
Messages
5,489,659
Members
407,703
Latest member
Chibuzo

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top