VBA- Macro for copy and paste of dynamic info in certain sheets into summary sheet

[Theresa.L.Waysone]

Hey all,

First question here and an issue that is melting my poor brain.

I have a Workbook and it has 6 tabs. These tabs are different divisions of a company. Each tab contains different headings like 'Employee Number' or 'First name' or 'Second name'. The heading aren't all in the same columns across the different tabs. (info being pulled from 6 different payrolls). The information is also changing on a monthly basis due to terminations and hires etc. The info is dynamic.

I want to consolidate these into one long list.

For example:

I want VBA to copy the info from column A in tab1 into Column A in tab7 (summary tab) and then copy the info from Column A in tab2 into the NEXT BLANK CELL in column A in tab7 and so on and so forth for the rest of the divisional tabs.

Finally Id like to be left with one unbroken list of all the info I need. I hope to be able to run a macro each month for this to save all the time wasting copying and pasting.

Would really appreciate some help. So far my efforts have ended in frustration.

Here is what Ive been trying so far:

Sub Test2()
'
' Test2 Macro
'Dim s1 As Excel.Worksheet
Dim s2 As Excel.Worksheet
Dim iLastCellS2 As Excel.Range
Dim iLastRowS1 As Long

Set s1 = Sheets("BaulderStone")
Set s2 = Sheets("Flattened Contribution File ")

'iLastRowS1 = s1.Cells(s1.Rows.Count, "A").End(xlUp).Row

'Set iLastCellS2 = s2.Cells(s2.Rows.Count, "A").End(xlUp).Offset(1, 0)

's1.Range("A1", s1.Cells(iLastRowS1, "A")).Copy iLastCellS2

'Dim s3 As Excel.Worksheet
Dim s2 As Excel.Worksheet
Dim iLastCellS2 As Excel.Range
Dim iLastRowS1 As Long
Set s3 = Sheets("Retirement Living")
Set s2 = Sheets("Flattened Contribution File ")

' iLastRowS3 = s3.Cells(s1.Rows.Count, "D").End(xlUp).Row

' Set iLastCellS2 = s2.Cells(s2.Rows.Count, "A").End(xlUp).Offset(1, 0)

's3.Range("A1", s3.Cells(iLastRowS3, "A")).Copy iLastCellS2
'
End Sub

[Debraj Roy]

Hi Theresa.L.Waysone !

Welcome to the forum..

"Ron de Bruin" has done mastering to overcome this type of situation..

try this add-in..
http://www.rondebruin.nl/win/addins/rdbmerge.htm

let us know, if you are facing any issue..