VBA Counter print and copy data to new sheet

dodo2407

New Member
Joined
Jul 27, 2022
Messages
2
Office Version
  1. 2021
Platform
  1. Windows
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.


1-min.JPG


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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top