PLS Help For Each loop (detailed info of the problem)

[JoaoNorris]

Hey guys,


I'm working on a macro that automates the process of using and existing excel. It consists of two worksheets where in one you paste data with time steps of 1 hour, and using some formulas, on the other sheet you have the conversion of that data to time steps of 15 minutes. And you had to do it manually.


I already worked on how to import the files you want to convert, let's say 12 months (We will have 12 worksheets plus one auxiliary and the two initial existing ones).This is how it looks. Note that "PVGIS4" and "PVGIS5" are the pre-existing worksheets.


1.jpg

So now I'm working on the for each loop that goes through all the worksheets that are not named "PVGIS4" or "PVGIS5". I have to do some actions - text to columns, due to the format of these files - and then selecting the used range and pasting it in "PVGIS5". The result will be that in "PVGIS4" we will have this data in time steps of 15mins. For last, it needs to copy the data in this worksheet and posting it on the sheet of the loop we're on, let's say december.
This is part of the code I worked on, it includes the loop:

For Each ws In ThisWorkbook.Worksheets 'This statement starts the loop
    
        If ws.name <> "PVGIS5" And ws.name <> "PVGIS4" And ws.name <> "auxiliar" Then
       
              Application.DisplayAlerts = False
              ActiveSheet.Columns(1).TextToColumns Other:=True, OtherChar:=";"
              ActiveSheet.UsedRange.Copy Worksheets("PVGIS5").Range("B2")
             Application.DisplayAlerts = True
             Worksheets("PVGIS4").UsedRange.Copy ActiveSheet.Range("A1")
       
              On Error Resume Next
                     ActiveSheet.ShowAllData
              On Error GoTo 0


  '1. Apply Filter
                  ActiveSheet.Range("A9:G1000").AutoFilter Field:=3, Criteria1:="0"
  
  '2. Delete Rows
                  Application.DisplayAlerts = False
                      ActiveSheet.Range("A9:G1000").SpecialCells(xlCellTypeVisible).Delete
                  Application.DisplayAlerts = True
  
  '3. Clear Filter
              On Error Resume Next
                     ActiveSheet.ShowAllData
              On Error GoTo 0

             'Autofilter
             'Delete rows
             'save ws


              'If ActiveSheet.Index = Worksheets.Count Then
                        'Worksheets(1).Activate
              'Else
                        ActiveSheet.Next.Activate
              'End If
        End If
Next ws

The problem I'm facing is that it all goes well for the first iteration (December). Then it goes to next ws, all fine, but when "ActiveSheet.UsedRange.Copy Worksheets("PVGIS5").Range("B2")" I reach this line of code during execution it pastes the November data in the November sheet but also on the December one, like on the printscreen below.
1.jpg

So in the end of the 12 months, I have 12 times the January sheet, that is the last.

I'm guessing it's way i'm selecting the ranges the wrong way, or I'm just not comprehending the way the for each loop works, since I'm a newbie with VBA .

Sorry for the long post.

Really hope someone can help.

[Paul_Hossler]

Without a sample workbook, this is only a guess based on the partial macro

You have ws as worksheet, but use ActiveSheet in the macro

ws <> ActiveSheet

ActiveSheet is which ever sheet is on top in Excel, and probably stays active for all iterations of the loop

    For Each ws In ThisWorkbook.Worksheets 'This statement starts the loop


        If ws.Name <> "PVGIS5" And ws.Name <> "PVGIS4" And ws.Name <> "auxiliar" Then
            Application.DisplayAlerts = False
            ActiveSheet.Columns(1).TextToColumns Other:=True, OtherChar:=";"
            ActiveSheet.UsedRange.Copy Worksheets("PVGIS5").Range("B2")

Use ws when you want the current sheet in the loop

    For Each ws In ThisWorkbook.Worksheets 'This statement starts the loop


        If ws.Name <> "PVGIS5" And ws.Name <> "PVGIS4" And ws.Name <> "auxiliar" Then
            Application.DisplayAlerts = False
            ws.Columns(1).TextToColumns Other:=True, OtherChar:=";"
            ws.UsedRange.Copy Worksheets("PVGIS5").Range("B2")

[JoaoNorris]

Hey Paul,

Thank you for your answer,

I initially had the loop like you suggested:

For Each ws In ThisWorkbook.Worksheets 'This statement starts the loop
    
    If ws.name <> "PVGIS5" And ws.name <> "PVGIS4" And ws.name <> "auxiliar" Then 'Perform the Excel action you wish (turn cell yellow below)
       
       Application.DisplayAlerts = False
        ws.Columns(1).TextToColumns Other:=True, OtherChar:=";"
        ws.UsedRange.Copy Worksheets("PVGIS5").Range("B2")
       Application.DisplayAlerts = True
       'ThisWorkbook.Worksheets("PVGIS4").Range("B2").Paste
       Worksheets("PVGIS4").UsedRange.Copy ws.UsedRange
       
        On Error Resume Next
            ws.ShowAllData
        On Error GoTo 0


  '1. Apply Filter
            ws.Range("A9:G1000").AutoFilter Field:=3, Criteria1:="0"
  
  '2. Delete Rows
            Application.DisplayAlerts = False
             ws.Range("A9:G1000").SpecialCells(xlCellTypeVisible).Delete
            Application.DisplayAlerts = True
  
  '3. Clear Filter
        On Error Resume Next
            ws.ShowAllData
        On Error GoTo 0
       'Autofilter
       'Delete rows
       'save ws


    End If
Next ws




End Sub

It was doing the same as the one i posted on the first thread, but the active worksheet was always the one where the loop starts.

Attachment 26154

As you can see here, it's the beginning of the 3rd iteration (October), the selected worksheet is December, and the data pasted on this worksheet is from November(2nd iteration). At the end of this iteration it will have data from October aswell as the sheet of November.

I then added these few lines so that the selected sheet follwed the iterations, but the main problem persisted.The changes are being made in the sheet where the cycle is and all the previous ones.

        'If ActiveSheet.Index = Worksheets.Count Then
            'Worksheets(1).Activate
        'Else
            ActiveSheet.Next.Activate
        'End If

[Paul_Hossler]

I got an Invalid Attachment error when I tried to open your attachment

Try to post it again



However

Worksheets("PVGIS4").UsedRange.Copy ws.UsedRange

Do you want to copy the same PVGIS4 data to each worksheet?

Record a macro with just the copy / paste you want and compare the code

[JoaoNorris]

I don't know what happened to the attachment, hope this time works:

"It was doing the same as the one i posted on the first thread, but the active worksheet was always the one where the loop starts.

5.jpg

As you can see here, it's the beginning of the 3rd iteration (October), the selected worksheet is December, and the data pasted on this worksheet is from November(2nd iteration). At the end of this iteration it will have data from October aswell as the sheet of November.

I then added these few lines so that the selected sheet follwed the iterations, but the main problem persisted.The changes are being made in the sheet where the cycle is and all the previous ones."

        'If ActiveSheet.Index = Worksheets.Count Then
            'Worksheets(1).Activate
        'Else
            ActiveSheet.Next.Activate
        'End If

As you can see from the code I uploaded in the previous post, it first copies the data from the active ws and pastes it in PVGIS5. In PVGIS5 I have a set of formulas (in the excel sheet, not the vba code), that arranges the data as I want (Data from a day in time steps of 1hour to data from that day in time steps of 15min). The result appears in PVGIS4, that's why I'm initially pasting in PVGIS5 (from WS to PVGIS5) and then another Copy/paste ( from PVGIS4 to WS) so I get the result I want.

I can't really understand what you meant with "Record a macro with just the copy / paste you want and compare the code", my english is not so good maybe, could you explain it for dummies?

[Paul_Hossler]

1. Still no attachment

2. Two ways to start and stop the macro recorder

Capture.JPG Capture1.JPG




The recorder captures (almost) everything and is very specific as to ranges, etc. so there is a lot of fix up required

Normally do not need to .Select or .Activate something

Macro1 is what therecorder captured, and the other one is a cleanup and generaliztion

Option Explicit


Sub Macro1()
'
' Macro1 Macro
'


'
    Range("A1").Select
    Selection.CurrentRegion.Select
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub




Sub Macro1_Generalized()
    Worksheets("Sheet1").Range("A1").CurrentRegion.Copy Sheets("Sheet2").Range("A1")
    Application.CutCopyMode = False
End Sub

[JoaoNorris]

Hey Paul,

I was just posting a new reply since I may have just found what the problem is. When it is pasting in my sheet "ws", it is doing so with the formulas from PVGIS4, which have references to PVGIS5 aswell. So I'll try to paste just the values and see how it works. If it does work i'll mark this thread as SOLVED.
Thank for your help, even got me to know the recorder.
Now I'll have to save each worksheet modified as a txt. file, any insight on this?

[Paul_Hossler]

I used the recorder to just copy one worksheet to a new workbook, and then save that workbook as text file and modified / cleanedup / expanded the code

Added the 'Kill' in case he txt file existed

There's a difference between ThisWorkbook and ActiveWorkbook, so I've found it better to use a workbook variable to clearly identify wha in this case is a temporary one

Option Explicit




'From Recorder
Sub Macro2()
'
' Macro2 Macro
'


'
    Sheets("Jan").Select
    Sheets("Jan").Copy
    ActiveWorkbook.SaveAs Filename:="C:\Users\Daddy\Documents\Jan.txt", _
        FileFormat:=xlTextMSDOS, CreateBackup:=False
    ActiveWindow.Close
End Sub




Sub Macro2_Modified()
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim s As String
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    For Each ws In ThisWorkbook.Worksheets
        s = ThisWorkbook.Path & "\" & ws.Name & ".txt"
        
        On Error Resume Next
        Kill s
        On Error GoTo 0
        
        
        ws.Copy
        Set wb = ActiveWorkbook
        wb.SaveAs Filename:=s, FileFormat:=xlTextMSDOS, CreateBackup:=False
        ActiveWindow.Close
        
        ThisWorkbook.Activate
    Next


    Application.EnableEvents = True
    Application.ScreenUpdating = True


    MsgBox "Done"


End Sub

[JoaoNorris]

This worked so well, thank you so much for your help!