Taking values from visible cells

[satya12]

Hi Team,

i am having data like this in 1 excel file will have 2000 records In BK col we have to filter only "IAUT" data. till this i am having VBA code, its filtering correctly. but when pasting data it is not taking visible cells, only taking 1 cell value to all the rows. please check and give me the data . need urgent

Sub RMG()

Dim IBG As String
Dim Comment As Range

Set Macro = Workbooks("RMG_Macro.xlsm")
Set Macrohome = Macro.Sheets("HOME")
Set Macrows = Macro.Sheets("Data")
Set RMG1 = Workbooks.Open(Filename:=(Macrohome.Range("F4").Value))
Set RMG1ws = RMG1.Sheets("RMG Asso Base Data Report_Deliv")

IBG = Macrohome.Range("P4").Value
RMG1ws.Range("$A$1 : $FZ$1000000").AutoFilter Field:=63, Criteria1:=IBG
a = RMG1ws.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To 100
With RMG1ws
    For Each Comment In .Range("BK1", .Cells(.Rows.Count, "BK").End(xlUp)).SpecialCells(xlCellTypeVisible)
    
        If InStr(Comment.Value, "IAUT") > 0 Then
            Macrows.Range("A" & i).Value = Comment.Offset(0, -2).Value
            Exit For
        End If
    Next
End With
    
    


Next i



End Sub

 

[bebo021999]

It seems you like to copy all filterred value into other sheet, cell A2

With RMG1ws
    .Range("BK1", .Cells(.Rows.Count, "BK").End(xlUp)).SpecialCells(xlCellTypeVisible).copy
     Macrows.Range("A2"). pastespecial  paste:=xlPasteAll
End With

 

[satya12]

Thanks for your reply! @bebo021999

After Filter i want to copy some columns like c,d e what are the visible cells in Main sheet and paste those columns into respective col(our choice).

See i filter IAUT in BK column, after that i need to copy C, D, E values data which are visible (like 4th row, 10th row) and paste those data into Macro sheet Col A,B,C

 

[Fluff]

How about

Sub RMG()

Dim IBG As String

Set Macro = Workbooks("RMG_Macro.xlsm")
Set Macrohome = Macro.Sheets("HOME")
Set Macrows = Macro.Sheets("Data")
Set RMG1 = Workbooks.Open(Filename:=(Macrohome.Range("F4").Value))
Set RMG1ws = RMG1.Sheets("RMG Asso Base Data Report_Deliv")

IBG = Macrohome.Range("P4").Value
With RMG1ws
   .Range("$A$1 : $FZ$1000000").AutoFilter Field:=63, Criteria1:=IBG
   .AutoFilter.Range.Offset(1).Columns("C:E").Copy Macrows.Range("A2")
End With
End Sub

 

[satya12]

Thanks @Fluff ! its working. but its not working when i added some extra columns. I want A to C columns after I want BF to BK col . i Tried this but getting error.

With RMG1ws
   .Range("$A$1 : $FZ$1000000").AutoFilter Field:=63, Criteria1:=IBG
   .AutoFilter.Range.Offset(1).Columns("A:C", "BF:BK").Copy Macrows.Range("A2")
End With
Next i

 

[Fluff]

If you want A:C & BF:BK, why did you say you wanted C:E?
Are these the correct columns, or are you going to change it again?

 

[satya12]

I want so many columns data in that file, i can't say i want this all, because i am also learning, without try how we will know which type of code will work or not.

I want A:C & N & P& AC&AF&AK &AM&AN:AO& AV:AY &BA&BC:BD & BF(will have numeric, alph, Full stop):BI & CZ & DI:DJ & DP& DR & ED & ES:EX&FA&FH:FJ

See for all these col i want data , i am trying to learn how to do that

 

[Fluff]

You need to let us know EXACTLY which columns you want, otherwise we end-up constantly changing the code.

I want A:C & N & P& AC&AF&AK &AM&AN:AO& AV:AY &BA&BC:BD

Is the EXACT columns that you want, or could this change?

 

[satya12]

Okay next time i wont repeat !

yes those are the columns exactly.

 

[Fluff]

Ok, how about

Sub RMG()

Dim IBG As String

Set Macro = Workbooks("RMG_Macro.xlsm")
Set Macrohome = Macro.Sheets("HOME")
Set macrows = Sheets("Data")
Set RMG1 = Workbooks.Open(Filename:=(Macrohome.Range("F4").Value))
Set RMG1ws = RMG1.Sheets("RMG Asso Base Data Report_Deliv")

IBG = Macrohome.Range("P4").Value
With RMG1ws
   .Range("$A$1 : $FZ$1000000").AutoFilter Field:=63, Criteria1:=IBG
   With .AutoFilter.Range.Offset(1)
      Intersect(.EntireRow, Range("A:C,N:N,P:P,AC:AC,AF:AF,AK:AK,AM:AO,AV:AY,BA:BD,BF:BI,CZ:CZ,DI:DJ,DP:DP,DR:DR,ED:ED,ES:EX,FA:FA,FH:FJ").EntireColumn).Copy macrows.Range("A2")
   End With
End With
End Sub