Data summary - Macro changes !!!

0

Hi,

I have the following code. I have worksheets in almost same format except for a month where a new column is added. 

- I am using the following macro to copy each column of data from each worksheet in the workbook starting at column D into a newly created summary worksheet
- I have just tried it for couple of columns in the code but not getting the columns copied over for all the worksheets. Can you please help me to tweak this so this copy same column from all worksheets in the workbook and stack these beside each other so column D from sheet1 should be pasted beside column D from Sheet2

Sub Create_Summary()

Application.DisplayAlerts = False
On Error Resume Next
Sheets("Summary").Delete
Application.DisplayAlerts = True
n = Application.Worksheets.Count
Sheets.Add.Name = "Summary"
Sheets("Summary").Move after:=Worksheets(Worksheets.Count)

Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> "Summary" And sh.Name <> Sheets(n).Name Then
Set col = Columns(Columns.Count).End(xlToLeft)
sh.Range("D:D").Copy Destination:=Sheets("Summary").Range(col, col).Offset(0, 1)
End If
Next sh
Range("A:B").Insert Shift:=xlToRight
Sheets(n).Select
Range("F:F").Copy Destination:=Sheets("Summary").Range("A:A")


End Sub


I have also attached a sample worksheet containing just two months data:



Many thanks !!
Sophia
Post Edited
CODE Tags: You must add [CODE][/CODE] tags around your code! (click the CODE button to do this when creating a post)
Answer
Discuss

Discussion

Upload the file to the post instead of included a link to dropbox please.
don (rep: 1297) Sep 8, '16 at 10:02 pm
Next time, add CODE tags around your macro when you include the code in a post. I tried to do it for you but it moved it to one line. Just edit your question and put back in the macro INSIDE of code tags and this issue won't occur in the future.
don (rep: 1297) Sep 8, '16 at 10:05 pm
Add to Discussion

Answers

0
Selected Answer

Make sure to follow my comments for your post above!

To answer your question, I think you just need to remove this part:

And sh.Name <> Sheets(n).Name 
Discuss

Discussion

Thanks Don. Question edited !!

Also if you can suggest one more tweak. The code is currently taking just one column from each worksheet. I want it to take 2 columns but same Columns should be stacked together e.g. column D from sheet 1 beside column D from sheet 2, column E from sheet 1 beside column E from sheet 2 so on .....
ferocious12 (rep: 3) Sep 8, '16 at 10:33 pm
And you want those all on the same worksheet?
don (rep: 1297) Sep 12, '16 at 12:38 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login