macro to compile data from multiple worksheets

[eschwanz]

Hello all,
I have source data in multiple worksheets that i would like to compile into one worksheet. So to lay out the framework, i have worksheet 1,2, and 3 that contains the source data. The column data that I would like to compile is contained in Columns A,B,G and H, which some cells contain formulas. Sheet 4 is the destination. I would like to compile A,B,G and H from the source sheets into columns A,B,C, and D of sheet 4. I do not want the formulas carried over just the values from the source.

The data from Sheets 1,2,and 3 will be periodically updated so this macro will need to leave the source data, so i will likely have a start button to compile the data when the sources are updated.

Your help is appreciated.

Thank you,

 

[John Davis]

Does this help?

Sub eschwanz()
Dim i As Integer
Dim x As Long
Dim lr As Long

For i = 1 To 3

Sheets("Sheet" & i).Activate

lr = Cells(Rows.Count, 1).End(xlUp).Row

x = Range("A2:A" & lr).Rows.Count

Sheets("Sheet4").Range("A" & Rows.Count).End(3)(2).Resize(x).Value = Sheets("Sheet" & i).Range("A2:A" & lr).Value
Sheets("Sheet4").Range("B" & Rows.Count).End(3)(2).Resize(x).Value = Sheets("Sheet" & i).Range("B2:B" & lr).Value
Sheets("Sheet4").Range("C" & Rows.Count).End(3)(2).Resize(x).Value = Sheets("Sheet" & i).Range("G2:G" & lr).Value
Sheets("Sheet4").Range("D" & Rows.Count).End(3)(2).Resize(x).Value = Sheets("Sheet" & i).Range("H2:H" & lr).Value

Next i

End Sub

 

[eschwanz]

That is exactly what i am looking for thanks.

There are only a few functions that i will need to make this work for my specific function.

- the source data starts in row 3 of each of the source sheets, so when i run this it brings in the heading from each sheet. Is there a way to have the headings and formatting maintained in destination sheet?

- There will be multiple sheets in work book, some that are not source sheets, is there a way to specify which sheets to pull from by the name of each sheet?

Thanks

 

[John Davis]

This might take care of problem 1. For the other question I'd need to know which sheets to include or exclude;

Sub eschwanz()
Dim i As Integer
Dim x As Long
Dim lr As Long

For i = 1 To 3

Sheets("Sheet" & i).Activate

lr = Cells(Rows.Count, 1).End(xlUp).Row

x = Range("A3:A" & lr).Rows.Count

Sheets("Sheet4").Range("A" & Rows.Count).End(3)(2).Resize(x).Value = Sheets("Sheet" & i).Range("A3:A" & lr).Value
Sheets("Sheet4").Range("B" & Rows.Count).End(3)(2).Resize(x).Value = Sheets("Sheet" & i).Range("B3:B" & lr).Value
Sheets("Sheet4").Range("C" & Rows.Count).End(3)(2).Resize(x).Value = Sheets("Sheet" & i).Range("G3:G" & lr).Value
Sheets("Sheet4").Range("D" & Rows.Count).End(3)(2).Resize(x).Value = Sheets("Sheet" & i).Range("H3:H" & lr).Value

Next i

End Sub

 

[eschwanz]

Can the sheets be called out by name, or are they indexed by order of the tabs?

the titles are
- tank foundations (sheet 2)
- footings and bases (sheet 3)
- pedestals (sheet 4)
- SOGs and (sumps Sheet 5)

 

[eschwanz]

I worked my way through this one, thanks.

What code do i need to add to have the macro clear everything in sheet 4 and then populate with the data from 1,2, and 3?

Thank you.

 

[John Davis]

Just before the loop, Like this:

Sub eschwanz()
Dim i As Integer
Dim x As Long
Dim lr As Long

Sheets("Sheet4").cells.clearcontents

For i = 1 To 3

Sheets("Sheet" & i).Activate

lr = Cells(Rows.Count, 1).End(xlUp).Row

x = Range("A3:A" & lr).Rows.Count