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.


26148

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.
26147

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.

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.

26163

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? :D

[Paul_Hossler]

1. Still no attachment

2. Two ways to start and stop the macro recorder

26166 26167




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!