Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Excel Macro

0

I have a macro that will transfer data from one sheet to another but it does not transfer data to row 2 on the Staged sheet and does not transfer multiple rows at one time. It only transfers 4 or 5 rows and I need it to transfer up to 800 rows at a time. Below is my macro data. What am I missing for it to move up to 800 rows and to start moving on row 2 of sheet Staged?

Sub Cheezy()

'Updated by Kutools for Excel 2017/8/28

    Dim xRg As Range

    Dim xCell As Range

    Dim I As Long

    Dim J As Long

    Dim K As Long

    I = Worksheets("Sheet1").UsedRange.Rows.Count

    J = Worksheets("Sheet2").UsedRange.Rows.Count

    If J = 1 Then

       If Application.WorksheetFunction.CountA(Worksheets("Sheet2").UsedRange) = 0 Then J = 0

    End If

    Set xRg = Worksheets("Sheet1").Range("C1:C" & I)

    On Error Resume Next

    Application.ScreenUpdating = False

    For K = 1 To xRg.Count

        If CStr(xRg(K).Value) = "Done" Then

            xRg(K).EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A" & J + 1)

            xRg(K).EntireRow.Delete

            If CStr(xRg(K).Value) = "Done" Then

                K = K - 1

            End If

            J = J + 1

        End If

    Next

    Application.ScreenUpdating = True

End Sub

Answer
Discuss

Discussion

Hello jsolai, please edit your question to give it a descriptive title. "Excel macro" is too broad to be helpful to anyone wanting to learn or looking for an answer.
Variatus (rep: 4889) Jan 17, '20 at 5:50 am
Add to Discussion

Answers

0

I think your code is not very well written and unsuitable to be placed in the hands of a layman. Therefore it's no fun to modify it and I have chosen to re-write instead.

Option Explicit

Private Enum Nws                            ' set your source parameters here
    NwsFirstDataRow = 2
    NwsFirstDataColumn = 1                  ' 1 = column A, 2 = B etc
    NwsLongestRow = 1
    NwsLongestColumn = 1
End Enum

Sub TransferData()
    ' Variatus @TeachExcel
    ' 17 Jan 2020

    Const Destination As String = "C4"      ' first destination cell in Target sheet
                                            ' change as desired
    Dim WsS As Worksheet, WsT As Worksheet
    Dim Arr As Variant
    Dim RngS As Range, RngT As Range
    Dim Rl As Long, Cl As Long              ' Last row, last column

    Set WsS = Worksheets("Sheet1")          ' Source: modify name as desired
    Set WsT = Worksheets("Sheet2")          ' Target: modify name as desired


    ' Never touch the code below. Do all modification above this line.
    With WsS
        Rl = .Cells(.Rows.Count, NwsLongestColumn).End(xlUp).Row
        Cl = .Cells(NwsLongestRow, .Columns.Count).End(xlToLeft).Column
        ' determine the range to be copied from
        Set RngS = .Range(.Cells(NwsFirstDataRow, NwsFirstDataColumn), _
                          .Cells(Rl, Cl))
    End With

    With WsT
        Rl = .Cells(.Rows.Count, .Range(Destination).Column).End(xlUp).Row + 1
        If Rl < .Range(Destination).Row Then Rl = .Range(Destination).Row
        Set RngT = .Cells(Rl, .Range(Destination).Column)
    End With

    With RngS
        Arr = .Value
        RngT.Resize(.Rows.Count, .Columns.Count).Value = Arr
    End With
End Sub

In fact, your code is a perfect demonstration of how NOT to write code.

  1. Variables are named as x, I, J, K.
    Best practise is to give meaningful names.
  2. Parameters are written directly into the code.
    Best practise is to separate parameters from the code so that the user doesn't need to modify the code when he wants to modify the parameters.
  3. Modification of the data is required ("Done" to be found in column "K" - which isn't the worksheet column K).
    Best practise is to never modify the data.

Not that your code might not function. Rather, a combination of the above faults has rendered it useless to you and forced you to seek help. I hope this won't happen with the code I wrote.

Before you start, please set the parameters. There are seven of them.

  1. There are 4 enumerations at the top.
    1. Set the first row of data you wish to copy and the first column.
    2. Then identify the longest column and the longest row.
      In the example in the attached workbook columns A and B are of different lengths. Try the result if your set NwsLongstColumn to 1 or 2. Same for the rows. In most cases column A and row 1 will be the longest. This depends upon your data.
      The effect is that data in the longest column and columns to the left of it will be copied, and data starting from the FirstDataRow to the last row in the longest column.
      The Kutools code uses the UsedRange to determine the range to copy. That has other drawbacks.
  2. Set the constant Destination. This should be the top left cell of where you want the data to be pasted. In my example this cell is defined as C4. In your code the UsedRange is used to find a row in column A. UsedRange.Row may give the wrong result and you need to look in the code to modify the column. With my code you can change the target column but that column is used to determine the last used row too. If you are pasting data of different number of rows in some of the columns my arrangement will lead to data being over-written if the Destination column isn't the longest column. This is a conflict between determining the source and the target ranges. Some modification of the code would be required if this is relevant to you.
  3. Insert the names of the two worksheets, Source and Target. These names must match actual names in your workbook.

I suggest you play around with the parameters a little to familiarise yourself with the functionality. After that, set the parameters for what you need and forget them. Chances are, once set you will never need them again.

One more thing. This code works very fast because it copies only data, no formatting. If you need the data in the target sheet to be formatted the best way is to designate the first data row (the one indicated by the Destination constant) in the target sheet as a sample and add code to the above procedure to copy formats from there to the rest of the sheet. In that way your target sheet would be independent from the formats of imported data and changing the formats in that row would automatically be trasnsferred to all the sheet or only the new additions, according to a rule you would yet have to decide.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login