S
S
Shing2021-12-05 00:11:45
Macros
Shing, 2021-12-05 00:11:45

Why doesn't the macro copy all columns?

There is such a macro, its ultimate task is to copy / paste the entire lines, if they match, from sheet to sheet, with all columns.
I set For i = 1 To 100, that is, it should take like 100 columns.
And it eventually stops up to the fortieth column somewhere ... the rest of the tail does not copy.

Worksheets(nl2).Activate
  lr2 = Cells(Rows.Count, nk2).End(xlUp).Row
  c = 1
  For i = 1 To 100
    If Cells(1, i) <> "" Then c = i
  Next i
  Worksheets(nl1).Activate
  lr = Cells(Rows.Count, nk1).End(xlUp).Row

  For i = 1 To lr
    s1 = LCase(Trim(Cells(i, nk1)))
    s1 = Replace(s1, "-", " ")
    kn = nkr
    For j = 1 To lr2
      s2 = LCase(Trim(Worksheets(nl2).Cells(j, nk2)))
      s2 = Replace(s2, "-", " ")
          
      If s1 = s2 Then
        Worksheets(nl2).Select
        Range(Cells(j, 1), Cells(j, c)).Select
        Application.CutCopyMode = False
        Selection.Copy
        Worksheets(nlr).Select
        Range(Cells(i, kn), Cells(i, kn)).Select
        ActiveSheet.Paste
        kn = kn + c
      End If
    Next j
  Next i
  Worksheets(nlr).Activate
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  UserForm1.Hide

Answer the question

In order to leave comments, you need to log in

1 answer(s)
A
akelsey, 2021-12-06
@Shing

Based on:

And it eventually stops until the fortieth column somewhere ... the rest of the tail does not copy.

and a piece of code:
If Cells(1, i) <> "" Then c = i
and not knowing what is there on the sheet, I would venture to suggest that there is no value in the fortieth column in the first cell?

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question