dhruvgarg
03-12-2018, 08:44 PM
Hi,
I'm just beginning to VBA and was trying to see if I can set up a macro to help me with the file I'm working on.
I have a master sheet with a list of tasks, categorized based on certain codes ('AZ', 'SC', 'PR' and 'MP'). I want to set up macros so that when I click the button, it copies the data for each category into a new worksheet, and another macro to compile the data back to into the master file.
I've been looking at it as a two step process, one where the data in the master file gets sorted into the four worksheets based on the code, and two where the data gets refreshed in the master file if there are any changes made in the code specific worksheets.
The code I've written to copy the data into the four sheets works but only partially, in that only the first row of the data gets copied and I'm not too sure how to fix that. The following is the code that I've written so far:
Sub AZ()
Dim LastRow As Integer, i As Integer, erow As Integer
LastRow = Worksheets("Master_File").Range("A" & Rows.Count).End(xlUp).Row
For i = 3 To LastRow
If Cells(i, 2).Value = "AZ" Then
Range(Cells(i, 1), Cells(i, 11)).Select
Selection.Copy
Worksheets("AZ").Select
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
ActiveWorkbook.Save
Application.CutCopyMode = False
End If
Next i
End Sub
Would love any helps and tips regarding this.
I'm just beginning to VBA and was trying to see if I can set up a macro to help me with the file I'm working on.
I have a master sheet with a list of tasks, categorized based on certain codes ('AZ', 'SC', 'PR' and 'MP'). I want to set up macros so that when I click the button, it copies the data for each category into a new worksheet, and another macro to compile the data back to into the master file.
I've been looking at it as a two step process, one where the data in the master file gets sorted into the four worksheets based on the code, and two where the data gets refreshed in the master file if there are any changes made in the code specific worksheets.
The code I've written to copy the data into the four sheets works but only partially, in that only the first row of the data gets copied and I'm not too sure how to fix that. The following is the code that I've written so far:
Sub AZ()
Dim LastRow As Integer, i As Integer, erow As Integer
LastRow = Worksheets("Master_File").Range("A" & Rows.Count).End(xlUp).Row
For i = 3 To LastRow
If Cells(i, 2).Value = "AZ" Then
Range(Cells(i, 1), Cells(i, 11)).Select
Selection.Copy
Worksheets("AZ").Select
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
ActiveWorkbook.Save
Application.CutCopyMode = False
End If
Next i
End Sub
Would love any helps and tips regarding this.