Macro to go down column, check for specific value, and if so, paste another value on same row to another sheet

[ChapmanHomeAndLiving]

Hi! So i am fairly new to Macro's but am also running into SO many errors so much that i basically need to scrap everything complex and start over.... can i get some help?


I can adjust the actual rows and column numbers so i will keep it simple. Here is what i need to do:

Press a Command button on sheet 1
set active sheet to sheet 2
select column 10, row 1

IF - active cell has specific string "XYZ",
copy column 1 2 and 3 of row 1 and past into sheet 1
go down one row
repeat
Else - go down one row,
repeat


repeat a total of 100 times

can anyone help with this..?

 

[Fluff]

Hi & welcome to MrExcel.
If you have a header row in row 1 try

Sub Chapman()
   With Sheets("Sheet2")
      .Range("A1:J1").AutoFilter 10, "redbourn"
      Intersect(.AutoFilter.Range, .Range("A:C")).Copy Sheets("sheet1").Range("A1")
      .AutoFilterMode = False
   End With
End Sub

 

[ChapmanHomeAndLiving]

Thanks for the info! to be honest.... that went straight over my head though....


this is what i have so far.... care to give an opinion?

Private Sub CommandButton1_Click()


Dim ws As Worksheet
 'Dim for Raw Data
    Dim Ry As Integer
        Ry = 22 'Row number for Raw Data
    Dim Rdy As Integer
        Rdy = 23 'If/then factor
    Dim Ran As Integer
        Ran = 2 'To be Coppied
    Dim Rut As Integer
        Rut = 4 'To be Coppied
    Dim Rmr As Integer
        Rmr = 9 'To be Coppied
    Dim Rsf As Integer
        Rsf = 10 'To be Coppied


  'Dim for Summary Data
    Dim Sy As Integer
        Sy = 5 'Row number for Summary Data
    Dim San As Integer
        San = 1 'To be Pasted
    Dim Sut As Integer
        Sut = 2 'To be Pasted
    Dim Smr As Integer
        Smr = 3 'To be Pasted
    Dim Ssf As Integer
        Ssf = 4 'To be Pasted
        
    
 'Verify if Ready
 ' struggling with this part
        'Start loop
        
        
        'While Ry<100, IF cells(Ry,Rdy) = "XYZ", then Copy Paste Section, else Ry = Ry+1
 
 
 'Copy / Paste
     
   
    Worksheets("Hot Sheet").Cells(Sy, San).Value = Worksheets("Apartment Status Summary").Cells(Ry, Ran).Value
    Worksheets("Hot Sheet").Cells(Sy, Sut).Value = Worksheets("Apartment Status Summary").Cells(Ry, Rut).Value
    Worksheets("Hot Sheet").Cells(Sy, Smr).Value = Worksheets("Apartment Status Summary").Cells(Ry, Rmr).Value
    Worksheets("Hot Sheet").Cells(Sy, Ssf).Value = Worksheets("Apartment Status Summary").Cells(Ry, Rsf).Value
    
    Ry = Ry + 1
    
    'Repeat loop until Ry is 100




End Sub

Hi & welcome to MrExcel.
If you have a header row in row 1 try

Sub Chapman()
   With Sheets("Sheet2")
      .Range("A1:J1").AutoFilter 10, "redbourn"
      Intersect(.AutoFilter.Range, .Range("A:C")).Copy Sheets("sheet1").Range("A1")
      .AutoFilterMode = False
   End With
End Sub

 

[Fluff]

Please explain in words what you are trying to do.

 

[DanteAmor]

I put the macro working

Private Sub CommandButton1_Click()


    Dim ws1 As Worksheet, ws2 As Worksheet
    'Dim for Raw Data
    Dim Ry As Integer
        Ry = 22 'Row number for Raw Data
    'Dim for Summary Data
    Dim Sy As Integer
        Sy = 5 'Row number for Summary Data
        
    Set ws1 = Sheets("Apartment Status Summary")
    Set ws2 = Sheets("Hot Sheet")
        
    While Ry < 100
        If ws1.Cells(Ry, 23) = "XYZ" Then
            ws2.Cells(Sy, 1).Value = ws1.Cells(Ry, 2).Value     'ran
            ws2.Cells(Sy, 2).Value = ws1.Cells(Ry, 3).Value     'rut
            ws2.Cells(Sy, 3).Value = ws1.Cells(Ry, 4).Value     'rmr
            ws2.Cells(Sy, 4).Value = ws1.Cells(Ry, 10).Value    'rsf
            Sy = Sy + 1
        End If
        Ry = Ry + 1
    Wend
    MsgBox "Done"
End Sub

But you should consider the @Fluff option, filter the information by your string "XYX" (redbourn), then copy the information in the destination sheet.

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...ue-and-if-so-paste-another-value-on-same.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.