Copy and paste data from different sheet using vba [Closed]

Posts
2
Registration date
Thursday May 10, 2018
Status
Member
Last seen
May 12, 2018
-
Hi,

I have spent a lot of time to copy data from different sheets to a destination sheet using excel VB by selecting a region from a dropdown list.

I have 4 region tabs (Dubai, Cyprus, India and Africa) where data can be input.
and the 5th tab is master tab where the corresponding data to be displayed by selecting the region from dropdown list.

input tabs have data from col A:AT from where col A:L to be copied and paste in master tab by selecting the region.

Your help will be highly appreciated,

Thanks,
Sarfaraz.
See more 

2 replies

Posts
1248
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 17, 2019
205
0
Thank you
Hello Sarfaraz,

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.
sarfaraz416575
Posts
2
Registration date
Thursday May 10, 2018
Status
Member
Last seen
May 12, 2018
-
Hi Vcoolio,

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.
Posts
1248
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 17, 2019
205
0
Thank you
Hello Sarfaraz,


"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.