Copy data to another worksheet if Column B = Server 1

I have a table which has the name of the server in Column B.  I would like to copy Column C:D when Column B = "Server 1" and paste the data into another worksheet.

For example:

Column B                    Column C                               Column D
Server 1                       Data to Copy 1                      Yes
Server 1                       Data to Copy 2                      Yes
Server 1                       Data to Copy 3                      Yes
Server 2                       Data to Copy 1                      No
Server 2                       Data to Copy 2                      No

Any help you could give me would be greatly appreciated.

Thanks
Sonia
Sonia BowditchInformation Security OfficerAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
Please try this...
Sub CopyServer1Data()
Dim sws As Worksheet, dws As Worksheet
Dim lr As Long

Application.ScreenUpdating = False

Set sws = Sheets("Sheet1")  'Sheet with Data
lr = sws.Cells(Rows.Count, 2).End(xlUp).Row

On Error Resume Next
Set dws = Sheets("Server1") 'Output Sheet
dws.Cells.Clear
On Error GoTo 0

If dws Is Nothing Then
    Set dws = Sheets.Add(after:=sws)
    dws.Name = "Server1"    'Output Sheet
End If

'Assuming Row1 is the header row on Data Sheet
With sws.Range("A1").CurrentRegion
    .AutoFilter field:=2, Criteria1:="Server 1"     'Filtering column B with Server 1
    sws.Range("C1:D" & lr).SpecialCells(xlCellTypeVisible).Copy dws.Range("A1")
    .AutoFilter
End With
dws.Columns.AutoFit
dws.Activate
Application.ScreenUpdating = True
End Sub

Open in new window

In the attached, click the button "Copy Data" to run the code.
Book2.xlsm
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this...

Sub CopyServer1Data()
Dim sws As Worksheet, dws As Worksheet
Dim lr As Long

Application.ScreenUpdating = False

Set sws = Sheets("Sheet1")  'Sheet with Data
lr = sws.Cells(Rows.Count, 2).End(xlUp).Row

On Error Resume Next
Set dws = Sheets("Server1") 'Output Sheet
dws.Cells.Clear
On Error GoTo 0

If dws Is Nothing Then
    Set dws = Sheets.Add(after:=sws)
    dws.Name = "Server1"    'Output Sheet
End If

'Assuming Row1 is the header row on Data Sheet
With sws.Range("B1:B" & lr)
    .AutoFilter field:=1, Criteria1:="Server 1"     'Filtering column B with Server 1
    sws.Range("C1:D" & lr).SpecialCells(xlCellTypeVisible).Copy dws.Range("A1")
    .AutoFilter
End With
dws.Columns.AutoFit
Application.ScreenUpdating = True
End Sub

Open in new window

0
 
Sonia BowditchInformation Security OfficerAuthor Commented:
Thanks for taking a look at my issue.  The VBA works to select A1and copies that to a new sheet.  However, the entries for Server 1 are not getting picked up and copied along with the header.

Thanks.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
The code assumes that the servers are in column B, B1 is the header and will copy the data to new sheet if server is Server 1 not Server1.
Is it the case?
If it doesn't work, please upload a sample workbook.
0
 
Sonia BowditchInformation Security OfficerAuthor Commented:
Thanks for getting back to me.

I have uploaded a spreadsheet with data and formatting
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You forgot to upload. After attaching the file, you need to click on Upload button.
0
 
Sonia BowditchInformation Security OfficerAuthor Commented:
Apologies.  Attached now.
Book2.xlsx
0
 
Sonia BowditchInformation Security OfficerAuthor Commented:
Perfect.  Thank you very much for your help.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Sonia!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.