Copy and paste data from different sheet using vba
Closed
sarfaraz416575
Posts
2
Registration date
Thursday May 10, 2018
Status
Member
Last seen
May 12, 2018
-
Updated on May 10, 2018 at 05:43 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - May 12, 2018 at 03:45 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - May 12, 2018 at 03:45 AM
Related:
- Copy and paste data from different sheet using vba
- Vba case like - Guide
- Little alchemy cheat sheet - Guide
- How to transfer data from one sheet to another in excel using formula - Guide
- Number to words in excel without vba - Guide
- Google sheet right to left - Guide
2 responses
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
May 11, 2018 at 01:20 AM
May 11, 2018 at 01:20 AM
Hello Sarfaraz,
Try the following code:_
The code assumes:-
- All sheets have headings in row 1 and data starts in row 2.
- Your drop down list is in cell M1.
- When a region is selected from the drop down, you want the whole data set (from A:L) for that region transferred to the Master sheet.
No button is required with this code. It is a Worksheet_Change event which means that, when a region is selected from the drop down, and you click away (or press enter or down arrow), the code executes immediately.
Old data in the Master sheet is cleared every time that the code is executed.
To implement the code:-
- Right click on the Master tab.
- Select "View Code" from the menu that appears.
- In the big white field that then appears, paste the above code.
I hope that this helps.
Cheerio,
vcoolio.
Try the following code:_
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("M1")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub
Dim ws As Worksheet, lr As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set ws = Worksheets("Master")
ws.UsedRange.Offset(1).ClearContents
lr = Sheets(Target.Value).Range("A" & Rows.Count).End(xlUp).Row
Sheets(Target.Value).Range("A2:L" & lr).Copy
ws.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
ws.[M1].ClearContents
Application.DisplayAlerts = True
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
The code assumes:-
- All sheets have headings in row 1 and data starts in row 2.
- Your drop down list is in cell M1.
- When a region is selected from the drop down, you want the whole data set (from A:L) for that region transferred to the Master sheet.
No button is required with this code. It is a Worksheet_Change event which means that, when a region is selected from the drop down, and you click away (or press enter or down arrow), the code executes immediately.
Old data in the Master sheet is cleared every time that the code is executed.
To implement the code:-
- Right click on the Master tab.
- Select "View Code" from the menu that appears.
- In the big white field that then appears, paste the above code.
I hope that this helps.
Cheerio,
vcoolio.
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Updated on May 12, 2018 at 04:04 AM
Updated on May 12, 2018 at 04:04 AM
Hello Sarfaraz,
This is a very involved procedure which has been looked at a few times over the years. Rather than repeat that which has already been discussed on this topic, I'll refer you to the following link:-
https://forums.windowssecrets.com/showthread.php/168376-code-to-highlight-Excel-row-upon-hover
specifically post #11 which should be a resolution that you'd be happy with. You could re-start that same thread should you have any further questions. If you can't restart that thread, then start a new thread in that forum with a link to the other thread (the one above).
Cheerio,
vcoolio.
"Not sure how I do this, hope you can help me on this."
This is a very involved procedure which has been looked at a few times over the years. Rather than repeat that which has already been discussed on this topic, I'll refer you to the following link:-
https://forums.windowssecrets.com/showthread.php/168376-code-to-highlight-Excel-row-upon-hover
specifically post #11 which should be a resolution that you'd be happy with. You could re-start that same thread should you have any further questions. If you can't restart that thread, then start a new thread in that forum with a link to the other thread (the one above).
Cheerio,
vcoolio.
May 12, 2018 at 02:37 AM
Thank you so much for responding and figure out the solution.
Sorry I forgot to mention in my query, the input region sheet data starts from row number 3 and to be transfer to destination "Master" sheet where data starts from row number 5, col B.
I tried your code It works perfectly but not in the desired row and col.
however, I tried the following code it works perfectly through a combo box dropdown instead cell dropdown.
Private Sub combobox1_change()
If Me.ComboBox1.Value = "Dubai Office" Then
Sheets("Dubai Office").Range("a3:l10000").Copy Sheets("Master").Range("b5")
Application.ScreenUpdating = True
End If
If Me.ComboBox1.Value = "Maputo" Then
Sheets("Maputo").Range("a3:l10000").Copy Sheets("Master").Range("b5")
Application.ScreenUpdating = True
End If
If Me.ComboBox1.Value = "Cyprus" Then
Sheets("Cyprus").Range("a3:l10000").Copy Sheets("Master").Range("b5")
Application.ScreenUpdating = True
End If
If Me.ComboBox1.Value = "Australasia" Then
Sheets("Australasia").Range("a3:l10000").Copy Sheets("Master").Range("b5")
Application.ScreenUpdating = True
End If
End Sub
Above code works perfectly.
I have a new query, all data in input region sheets coming through a Userform which created in this workbook.
data in input region sheets from col A to L are either coming by combo box or text boxes from userform. and col M to AT by check boxes from Userform.
What I need to do is in master sheet I want to highlight the lines if the cursor placed on data and if click on any data cell of that line existing userform to be pop up with the corresponding data from input region sheet.
Not sure how I do this, hope you can help me on this.
Many thanks in advance.
Sarfaraz.