code for copying data from multiple sheets and compiling in a seperate sheet

[msawtell]

Hi all, new to this so please bear with me. My concern is a 2 parter but I'll only focus on part 1 at the moment.

I need a code preferably in the form of a button, that can copy all the data out of Sheet1,Sheet2,Sheet3....etc and paste it into sheet(COMPLETE). The data will always start from row 3 (below the headings) but will vary in length from sheet to sheet. In the COMPLETE sheet the data will start from row 3 (below the headings) and I want the data from each consecutive sheet to flow directly on from the previous sheets data. Also after i collate all the data I will need to add an extra column into column A position, hopefully this won't wreak havoc with the imported data.

If there is already a way this can be done with excel functions I would also love to hear about it.

I was thinking the first part of the solution was to select all non-blank cells and then paste. then repeat for the next sheet, and somehow paste to the first blank row.... seriously I am struggling.

 

[Michael M]

Maybe this to copy all data to the "COMPLETE" sheet

Sub MM2()
Dim ws  As Worksheet, LR1 As Long, LR2 As Long
Application.ScreenUpdating = False
For Each ws In Worksheets
    If ws.Name <> "COMPLETE" Then
        LR1 = Sheets("COMPLETE").Range("A" & Rows.Count).End(xlUp).Row + 1
        LR2 = ws.Range("A" & Rows.Count).End(xlUp).Row
        ws.Rows("3:" & LR2).Copy Destination:=Sheets("COMPLETE").Range("A" & LR1)
    End If
Next ws
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

Try this:Vba Script
Run this code from the sheet named Complete

Sub Copy_Me()
Application.ScreenUpdating = False
Dim i As Integer
Dim c As Integer
Dim Lastrow As Long
Dim Lastrow2 As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 2
    For c = 2 To Sheets.Count
        Lastrow2 = Sheets(c).Cells(Rows.Count, "A").End(xlUp).Row
            For i = 3 To Lastrow2
                Rows(Lastrow).Value = Sheets(c).Rows(i).Value
                Lastrow = Lastrow + 1
            Next
    Next
Application.ScreenUpdating = True
End Sub

 

[Michael M]

you don't need to loop through the rows, just copy the block of data from 3 to the last row in each sheet !!

 

[msawtell]

Thanks guys. I ran your code Michael M and it works. I just add the headings after running the code otherwise it gets a little funky. Thats an easy enough code for me to write. what about a code that inserts column A and applies a unique identifier to each row. for example.
column A column B column C etc
0601A1 data data data
0601A2 data data data
0601A3 data data data

and so on until the data stops.

 

[Michael M]

What is the unique identifier to be used ???

 

[msawtell]

For stage 2 of this dastardly situation. I am using VLOOKUP to extract info from this "COMPLETE" worksheet and place it into another worksheet. I need a unique value for VLOOKUP to work efficiently, apparently.

The hard part is that the data in column B, "power pole name" is what I end up having to relate over to my final worksheet. Its an audit of existing data. So I may have to INSERT or DELETE rows out of the final worksheet so that the correct number of rows are assigned per "power pole". and then the VLOOKUP can extract the columns as needed. I don't know how to do this part except by manually doing a FIND and then INSERTING or DELETING rows as required, I then copy and paste my unique identifier to a column at the end of the data set and the VLOOOKUP formula works. Time consuming....


You asked what it is used for

 

[Michael M]

You asked what it is used for
[/code]

No, I didn't....I asked what is the unique identifier to be used, not what is used for !
your 2nd paragraph makes absolutely no sense to me.....as I don't have to audit power poles ...

 

[msawtell]

OK my bad lol. bloody all day of excel makes you go crazy.

The unique identifier is just a so that each row has a unique name. I have made it a combination of the file name, in the above example (0601A) and i just started at 1 then dragged down all my rows of data.

yeah the 2nd paragraph is the bane of my existence atm.

Thanks for your help so far btw,I really appreciate it.

 

[Michael M]

Seriously....

bloody all day of excel makes you go crazy

That'd be my version of heaven for work !!!
Try...UNTESTED

Sub MM2()
Dim ws  As Worksheet, LR1 As Long, LR2 As Long
Application.ScreenUpdating = False
For Each ws In Worksheets
    If ws.Name <> "COMPLETE" Then
        LR1 = Sheets("COMPLETE").Range("A" & Rows.Count).End(xlUp).Row + 1
        LR2 = ws.Range("A" & Rows.Count).End(xlUp).Row
        ws.Rows("3:" & LR2).Copy Destination:=Sheets("COMPLETE").Range("A" & LR1)
    End If
Next ws
Sheets("complete").Activate
Columns("A:A").Insert
For c = 1 To LR1
    Range("A" & c).Value = "0601A" & c
Next c
Application.ScreenUpdating = True
End Sub