JoaoNorris
03-11-2020, 08:27 AM
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.
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.