Hello,
I am new to VBA and its boggling my head, I hope someone can shine some light
I have a dataset with multiple columns and rows as shown and I want to assign a counter that would turn from 0 to 1 for each row that has a "1" in column S.
Then I want the code to print a "1" in column AZ for every time the counter is 1.
After, I want to copy the full rows that have the newly assigned "1", in column AZ, and paste them into a new sheet called ExtractedData.
Code:
Sub ScanCode()
Dim Counter, Column, Row, Endrow
Dim Sheet As Worksheet
Counter = 0
Column = ("S")
Rows = 10000
Endrow = 11300
Do While Row < Endrow
If Sheet1.Cells(Row, Column) <> Sheet1.Cells(Row - 1, Column) Then Counter = Counter + 1
Row = Row + 1
Loop
If Counter = 1 Then
Rows(ActiveCell.Row).Select
Row (ActiveRow.Row(Value = "1"))
If Column("AZ") = 1 Then
.Copy
'create new sheet
Sheets.Add.Name = "ExtractedData"
'paste rows
Rows.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
I am new to VBA and its boggling my head, I hope someone can shine some light
I have a dataset with multiple columns and rows as shown and I want to assign a counter that would turn from 0 to 1 for each row that has a "1" in column S.
Then I want the code to print a "1" in column AZ for every time the counter is 1.
After, I want to copy the full rows that have the newly assigned "1", in column AZ, and paste them into a new sheet called ExtractedData.
Code:
Sub ScanCode()
Dim Counter, Column, Row, Endrow
Dim Sheet As Worksheet
Counter = 0
Column = ("S")
Rows = 10000
Endrow = 11300
Do While Row < Endrow
If Sheet1.Cells(Row, Column) <> Sheet1.Cells(Row - 1, Column) Then Counter = Counter + 1
Row = Row + 1
Loop
If Counter = 1 Then
Rows(ActiveCell.Row).Select
Row (ActiveRow.Row(Value = "1"))
If Column("AZ") = 1 Then
.Copy
'create new sheet
Sheets.Add.Name = "ExtractedData"
'paste rows
Rows.PasteSpecial Paste:=xlPasteValuesAndNumberFormats