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

[marcosis123]

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

 

[CSmith]

I would suggest a hotkey to do this, but it's possible with the

Worksheet.SelectionChange event

 

[marcosis123]

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]

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]

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:

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]

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]

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:

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]

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

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]

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]

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

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