Hidden Rows VBA

[SunnyAlv]

hello guys I'm in trouble, i hope u can help

I've seen the article from this Article and modified it in such a way, and I have one problem and I can't solve it

When VBA runs on Sheet1 it will copy the "Current Array" and paste it on the filtered Sheet2 but the problem is:

when pasting, VBA always starts pasting in the second ROW at"C2" even though I already set it in "Special Cell(Visible Row)" in the first row after filtering, should start pasting in ROW "C4" not "C2"

Sub sheet_to_sheet()

    Dim ws1, ws2 As Worksheet
    Dim rngA, rngB, R As Range
    Dim ra, rc As Long
  
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet2")
    Set rngA = ws1.Range("C2:G" & Cells(Rows.Count, "C").End(xlUp).Row)
    Set rngB = ws2.Range("C2:C" & Rows.Count).SpecialCells(xlVisible)(1)
    'ws2.AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible)
  
    ra = rngA.Rows.Count
    rc = rngA.Columns.Count
  
  
    ws2.Range("A1:G1").AutoFilter 7, ws1.Range("G2")
  
    If ra = 1 Then rngB.Resize(, rc).Value = rngA.Value: Exit Sub
 
    Set rngA = rngA.Cells(1, 1).Resize(ra, 1)
 
    For Each R In rngA
      rngB.Resize(1, rc).Value = R.Resize(1, rc).Value
        Do
          Set rngB = rngB.Offset(1, 0)
        Loop Until rngB.EntireRow.Hidden = False
    Next
 
    Application.GoTo rngB
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
  
Exit Sub
skip:
    If Err.Number <> 424 Then
        MsgBox "Error found: " & Err.Description
    End If
 
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
  
End Sub

 

[kevin9999]

What exactly are you trying to do? Seems like you're trying to copy values from sheet1 (columns C:G) to the first available row on sheet2 if they meet some condition?

 

[SunnyAlv]

should start pasting in ROW "C4" not "C2"

this is what I want to do @kevin9999

Look at row C2:G2 (Sheet2 After VBA Start) which has been automatically replaced with data from the first row of Sheet
because I have filtered Sheet2 with the Name Criteria in column "G" according to Sheet1, namely "R", and the name "R" starts at Row "C4:G4" in Sheet2

 

[kevin9999]

It looks like it's being pasted into C9 (according to your posted image)?

 

[SunnyAlv]

It looks like it's being pasted into C9 (according to your posted image)?

yes that's right but VBA starts from row "C2"

Look at row C2:G2 (Sheet2 After VBA Start) which has been automatically replaced with data from the first row of Sheet
because I have filtered Sheet2 with the Name Criteria in column "G" according to Sheet1, namely "R", and the name "R" starts at Row "C4:G4" in Sheet2

 

[kevin9999]

OK, I'm lost. I can't follow your logic at all, so I'm stepping out and hoping someone else will step in and assist. Best wishes!

 

[SunnyAlv]

OK, I'm lost. I can't follow your logic at all, so I'm stepping out and hoping someone else will step in and assist. Best wishes!

I'll make the logic easier wait a moment, and sorry if it's confusing

 

[SunnyAlv]

u can see image below @kevin9999
i hope this make sense

 

[kevin9999]

OK, that's different from the images you posted in post #1. So far you've said the data should be pasted in cell C4, then when I pointed out that your image indicated it was going into C9 you replied

yes that's right but VBA starts from row "C2"

I'll give this one last try, please answer the following questions & note that I'm not interested in your method, only your aim:
1. Are you trying to copy rows of data (columns C:G) from sheet 1 to sheet 2?
2. Do you want the data to be copied to the next available empty line on sheet 2 (irrespective of whether any filters are applied to sheet 2 or not)
3. What is the condition to copy rows from sheet 1 to sheet 2 - is it if they have an "R" in column G, or whatever value is in cell G2 on sheet 1, or whatever value is in cell G2 on sheet 2?

 

[SunnyAlv]

OK, that's different from the images you posted in post #1. So far you've said the data should be pasted in cell C4, then when I pointed out that your image indicated it was going into C9 you replied

I'll give this one last try, please answer the following questions & note that I'm not interested in your method, only your a
1. Are you trying to copy rows of data (columns C:G) from sheet 1 to sheet 2?
2. Do you want the data to be copied to the next available empty line on sheet 2 (irrespective of whether any filters are applied to sheet 2 or not)
3. What is the condition to copy rows from sheet 1 to sheet 2 - is it if they have an "R" in column G, or whatever value is in cell G2 on sheet 1, or whatever value is in cell G2 on sheet 2?

sorry to make you uncomfortable, I have got the answer, namely the filter that makes the code problematic

thanks @kevin9999

ws2.Range("A1:G1").AutoFilter 7, ws1.Range("G2")