Macro only works on first sheet

[matosimone]

Hi guys!

Im having some trouble with this macro. Ive merged 180 excel files (1 sheet per file) using a macro and it went fine. Now I need to perfom a simple copy paste in each sheet, im using the following macro, but it will only work in the first sheet. I can see it goes though all the different sheets (180), and I can even see it selects the right columns but nothing gets copy/pasted.

This is the code:

Sub Copy()

   Dim WS_Count As Integer
   Dim I As Integer


   WS_Count = ActiveWorkbook.Worksheets.Count


   For I = 1 To WS_Count


    Range("D:D,P:P,Q:Q").Select
    Selection.Copy
    Columns("V:V").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
        
   Next I


End Sub

Any ideas in what could be going wrong?
Thanks!
MAto

 

[matosimone]

Im trying this code now, but it only works in the selected worksheet, and not in all 180:

Sub Copy()

Dim ws As Worksheet
Application.ScreenUpdating = False


For Each ws In ThisWorkbook.Worksheets


    Range("D:D,P:P,Q:Q").Select
    Selection.Copy
    Columns("V:V").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A1").Select
        
    Next ws
    
    Application.ScreenUpdating = True
    
End Sub

 

[matosimone]

Works now! I leave the corrected code in case it helps someone:

Sub Copy()

Dim ws As Worksheet
Application.ScreenUpdating = False


For Each ws In ThisWorkbook.Worksheets


    ws.Activate
    Range("D:D,P:P,Q:Q").Select
    Selection.Copy
    Columns("V:V").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A1").Select
        
    Next ws
    
    Application.ScreenUpdating = True
    
End Sub

Now I need to bring the columns V, W and X from the 180 sheets to a MASTER sheet, so that everything is there and easy to operate with, any ideas?

What Im not sure how to solve is that is has to be pasted in the master every time in a different column, so that it wouldnt overwrite the previous pasted info.

 

[matosimone]

This is the code for copy/pasting every sheet into master sheet, the problem is that is pastes is into the same column all the time.

The logic should be that it should be pasted 4 columns to the right every new time, any ideas?

Sub CopyTOmaster()

Dim ws As Worksheet
Application.ScreenUpdating = False


    For Each ws In ThisWorkbook.Worksheets
    
    ws.Activate
    Columns("V:X").Select
    Selection.Copy
    
    Sheets("MASTER").Select
    
    Columns("A:A").Select
    ActiveSheet.Paste
    
    Next ws
    
    Application.ScreenUpdating = True
    
End Sub

 

[matosimone]

It feels a little lonely in here! haha but here's my solution:

Sub CopyTOmaster()

Dim ws As Worksheet
Dim Col As Integer
Col = 1


Application.ScreenUpdating = False


    For Each ws In ThisWorkbook.Worksheets
    
    ws.Activate
    Columns("V:X").Select
    Selection.Copy
    
    Sheets("MASTER").Select
    
    Columns(Col).Select
    ActiveSheet.Paste
    
    Col = Col + 4
    
    Next ws
    
    Application.ScreenUpdating = True
    
End Sub