code question

[camelia9]

Hello to all!!
I've got the code below

Sub CopyUnique()
Dim s1 As Worksheet, s2 As Worksheet
Set s1 = Sheets("mypage")
Set s2 = Sheets("george")
s1.Range("A3:L500").Copy s2.Range("A1")
s2.Range("A3:L500") .RemoveDuplicates Columns:=1, Header:=xlNo
End Sub


It works great. The thing is that i don't know how to adapt the code in order to copy data from more than one sheets. In this case i want to add more sheets to copy from than just from sheet "george" to the main sheet that is "my page". And one last thing, although i have added an additional module to place this code in, it doesn't work when i open the workbook unless i go into the vba editor and do it from there.
Any suggestions would be much appreciated...thank you for your kind intentions

 

[farmerscott]

Camellia,

Welcome to the forum.

Try this on a copy of your workbook.

Private Sub Workbook_Open()
Application.WindowState = xlMaximized

Dim ws As Worksheet
Dim ws2 As Worksheet
Dim lr As Long
Dim lr2 As Long

Set ws2 = Worksheets("mypage")

For Each ws In Worksheets
    If ws.Name <> "mypage" Then
            a = ws.Name
            lr = Sheets(a).Cells(Rows.Count, "A").End(xlUp).Row
            lr2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1
            Sheets(a).Range("A3:L" & lr).Copy ws2.Range("A" & lr2)
     End If
     Next ws
End Sub

FarmerScott

 

[camelia9]

Thank you!I'm very happy to be part of this forum!I've got 2 issues with the code you wrote down. The first is that it doesn't remove duplicates, so each time i log in the same data is copied all over again and the second is that the headings from each sheet is copied as well....

 

[farmerscott]

Camellia,

1. I have added the line to remove the duplicates.

2. what rows are your headers? I have assumed that they are in rows 1 and 2 based on your code. My code-

Sheets(a).Range("A3:L" & lr)

should copy anything from row 3 to the last row (where col A is not blank).

3. The "on open" code fires when you open the workbook. I assumed this as per your comment-

it doesn't work when i open the workbook unless i go into the <acronym title="visual basic for applications">vba</acronym> editor and do it from there.

There are a number of 'events' we can use to automatically fire, if that is your intention. Otherwise I suggest a button that you can click on your "mypage" sheet.

FarmerScott

 

[farmerscott]

The new code to include removing the duplicates is-

Private Sub Workbook_Open()
Application.WindowState = xlMaximized

Dim ws As Worksheet
Dim ws2 As Worksheet
Dim lr As Long
Dim lr2 As Long

Set ws2 = Worksheets("mypage")

For Each ws In Worksheets
    If ws.Name <> "mypage" Then
            a = ws.Name
            lr = Sheets(a).Cells(Rows.Count, "A").End(xlUp).Row
            lr2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1
            Sheets(a).Range("A3:L" & lr).Copy ws2.Range("A" & lr2)
     End If
     Next ws
lr = Sheets(a).Cells(Rows.Count, "A").End(xlUp).Row
ws2.Range("A3:L" & lr) .RemoveDuplicates Columns:=1, Header:=xlNo
End Sub

 

[camelia9]

Thank you very much farmerscott!!

 

[farmerscott]

Did you want to change it so the code did not fire upon opening the workbook?

 

[camelia9]

it's fine the way it is, thank you a lot once more!