Joining data from 4 tabs in a workbook

[paxodiva]

Hello All

Hope someone can help me with a problem i have.

There are 4 data tabs in my excel workbook and i would like to have the ability to amalgamate all the data into 1 sheet by running a macro
as there may be times when i will have added lines to the bottom of one of the sheets.

Could someone please tell me what the easiest way to do this

all help would be much appreciated
Diva

 

[paxodiva]

thank you ever so much for your help you have helped me no end
from a very happy person

thank you again

 

[paxodiva]

Hello everyone
I need a small change made to the code that VOG supplied that copies all spreadsheets into 1 and i dont know what to do.

Instead of copying the entire sheets into 1 i only want columns A to L copied from all the sheets into 1 sheet.

would be very grateful if someone can help me

 

[VoG]

Hi

Change

Set CopyRng = sh.UsedRange

to

Set CopyRng = Intersect(sh.Columns("A:L"), sh.UsedRange).Select

 

[paxodiva]

Sorry about this but i received a run time error
Unable to get the select property of the range class

 

[VoG]

Oops!

Set CopyRng = Intersect(sh.Columns("A:L"), sh.UsedRange)

 

[paxodiva]

thanks for that it worked that time however it is wiping out my VLOOKUPS that I have on the same page in Columns M to Q

Do you have any Ideas how this can not be removed

 

[VoG]

I don't understand why that would happen. Do you mean on the source sheet or the destination sheet.

 

[paxodiva]

Destination Sheet

 

[VoG]

I don't understand why that would happen since it is only copying and pasting columns A:L.

 

[VoG]

In response to your PM try

Sub Atest()
Dim LR As Long, c As Range
LR = Range("A" & Rows.Count).End(xlUp).Row
If LR < 2 Then Exit Sub
Columns("A").Insert
For Each c In Range("B1:C" & LR)
    With c
        If .Column = 2 Then Range("A" & .Row).Value = Left(.Value, 2)
        .Value = Replace(Right(.Value, Len(.Value) - 7), "-1-1", "")
    End With
Next c
Columns("D").Delete
End Sub