Macro to cycle the sheet

[michellin]

Hy there,

I'm really not good to cycle into the sheet to extract value.

I got sheet1 with a table from B4:L53, i need a macro to go down Colomn B till blank (sometimes i got 20 entry, sometimes 30 or 40. That's why i need the cycle option.
IF the macro find a value in the B column (between range 4 at 53), the macro need to create and copy on another sheet(all the value on same sheet, the new one) from b4.
here the cell he need to copy B, C, L.

So if in B4 we got a value, copy B4 ,C4 ,L4 on another sheet after the macro create the new sheet2, like B4 in (B4), C4 in (C4), L4 in (D4), then if he find on b5 copy on the same sheet we copy B4, C4, L4, like this B5 in (B5), C5 in(C5), L5 in (D5) till the last row with value.

I hope i'm clear

thanks in advance and if you know a course online free for cycling macro i will try to learn it

Michellin

 

[DanteAmor]

Hi @michellin . Thanks for posting on the board.

I show you 2 ways to do the search.

- One with a filter, you don't need a cycle, it's faster.
- And another with a cycle, if your interest is to learn to use cycles.​

In both macros you must adjust the name of the sheet where you have your data and of course, the value you want to search for.

Option 1 with filter.​

Sub copyvalues()
  Dim sh As Worksheet
  Dim lr As Long
  Dim f As Range
  Dim thevalue As String
  
  Set sh = Sheets("Sheet1") 'Fit the name of your sheet and value to find
  thevalue = "some"
  
  lr = sh.Range("B" & Rows.Count).End(3).Row
  Set f = sh.Range("B:B").Find(thevalue, , xlValues, xlWhole, , , False)
  If Not f Is Nothing Then
    sh.Range("B3:L" & lr).AutoFilter 1, thevalue
    Sheets.Add after:=Sheets(Sheets.Count)
    sh.Range("B4:C" & lr & ",L4:L" & lr).Copy Range("B4")
    sh.Range("B3").AutoFilter
  End If
End Sub

Option 2 with cycle.​

Sub copyvalues_cycle()
  Dim sh As Worksheet
  Dim lr As Long, i As Long, j As Long
  Dim f As Range
  Dim thevalue As String
  
  Set sh = Sheets("Sheet1") 'Fit the name of your sheet and value to find
  thevalue = "some"
  
  lr = sh.Range("B" & Rows.Count).End(3).Row
  j = 4
  Set f = sh.Range("B:B").Find(thevalue, , xlValues, xlWhole, , , False)
  If Not f Is Nothing Then
    Sheets.Add after:=Sheets(Sheets.Count)
    For i = 1 To lr
      If sh.Range("B" & i).Value = thevalue Then
        Range("B" & j).Value = sh.Range("B" & i).Value
        Range("C" & j).Value = sh.Range("C" & i).Value
        Range("D" & j).Value = sh.Range("L" & i).Value
        j = j + 1
      End If
    Next
  End If
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

 

[michellin]

Hi @michellin . Thanks for posting on the board.

I show you 2 ways to do the search.

- One with a filter, you don't need a cycle, it's faster.​

- And another with a cycle, if your interest is to learn to use cycles.​

In both macros you must adjust the name of the sheet where you have your data and of course, the value you want to search for.

Option 1 with filter.​

Sub copyvalues()
  Dim sh As Worksheet
  Dim lr As Long
  Dim f As Range
  Dim thevalue As String
 
  Set sh = Sheets("Sheet1") 'Fit the name of your sheet and value to find
  thevalue = "some"
 
  lr = sh.Range("B" & Rows.Count).End(3).Row
  Set f = sh.Range("B:B").Find(thevalue, , xlValues, xlWhole, , , False)
  If Not f Is Nothing Then
    sh.Range("B3:L" & lr).AutoFilter 1, thevalue
    Sheets.Add after:=Sheets(Sheets.Count)
    sh.Range("B4:C" & lr & ",L4:L" & lr).Copy Range("B4")
    sh.Range("B3").AutoFilter
  End If
End Sub

Option 2 with cycle.​

Sub copyvalues_cycle()
  Dim sh As Worksheet
  Dim lr As Long, i As Long, j As Long
  Dim f As Range
  Dim thevalue As String
 
  Set sh = Sheets("Sheet1") 'Fit the name of your sheet and value to find
  thevalue = "some"
 
  lr = sh.Range("B" & Rows.Count).End(3).Row
  j = 4
  Set f = sh.Range("B:B").Find(thevalue, , xlValues, xlWhole, , , False)
  If Not f Is Nothing Then
    Sheets.Add after:=Sheets(Sheets.Count)
    For i = 1 To lr
      If sh.Range("B" & i).Value = thevalue Then
        Range("B" & j).Value = sh.Range("B" & i).Value
        Range("C" & j).Value = sh.Range("C" & i).Value
        Range("D" & j).Value = sh.Range("L" & i).Value
        j = j + 1
      End If
    Next
  End If
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

Hy Dante Amor,

Thanks for the reply, i will try those for sure, but only one question in my head right now. I forgot to mention that in colomn B the value will be always a different 6 digit number, so i don't think i can put in : thevalue ="******".

I will look into your 2 macro, and i just learn about filter, i will read on this for sure. I was false thinking about that the only option on this was cycling through the sheet.

Thanks

Michellin

 

[DanteAmor]

I forgot to mention that in colomn B the value will be always a different 6 digit number, so i don't think i can put in : thevalue ="******".

Explain with examples what you have in the cell, what you are going to put as search value and what you expect as a result.

It is important that you give examples, since mentioning that you are going to search for "a value", believe me, does not help.

 

[michellin]

Explain with examples what you have in the cell, what you are going to put as search value and what you expect as a result.

It is important that you give examples, since mentioning that you are going to search for "a value", believe me, does not help.

Hy DanteAmor,

Your right, here a example, a print screen, sorry all the thing are write in french. The 6 digit code with vlookup search my info into our acces database.
It will be always a 6 digit number code like 680660 or 690153 or 698745. When i write the 6 digit code in column B, all of C, D, E, F, G, H, I, J, K, L, got a vlookup in those column .
But after that i want to copy only column B, C, L, in the next sheet into B, C, D, when the B column got the 6 digit number. It will never have a free space between. So as soon nothing is found in column B then close macro.
And copy the value not the vlookup.

Hope i more clear now, sorry for the confusion.

Michellin