VBA Row Number Adjustment

[HazatB]

Hi,

I have had help in earlier threads but kind of caused confusion with what I was asking so I started a new thread to properly ask for assistance.

I have created this macro below which has accomplished my desired task of copying data from one workbook into another, however, I need to figure out if there is a way to adjust for the number of rows whether there is less or more.


Workbooks("before_n_after_remap_audit_umroi.txt").Worksheets("before_n_after_remap_audit_umro").Range("A1:E6651").Copy
Workbooks("UMROI_Standard Cost Audit Reports.xlsm").Worksheets("Before n After Remap Review").Range("A7:E6657").PasteSpecial

Application.CutCopyMode = False

Workbooks("before_n_after_remap_audit_umroi.txt").Worksheets("before_n_after_remap_audit_umro").Range("F1:G6651").Copy
Workbooks("UMROI_Standard Cost Audit Reports.xlsm").Worksheets("Before n After Remap Review").Range("I7:J6657").PasteSpecial

Application.CutCopyMode = False

any assistance would be appreciated.

 

[MARK858]

Not sure exactly what you want by your description but try (untested)...

Sub Test()
    Dim LastRow As Long
    LastRow = Workbooks("before_n_after_remap_audit_umroi.txt").Worksheets("before_n_after_remap_audit_umro").Range("A:G").Find("*", , xlValues, xlPart, xlByRows, xlPrevious, False).Row

    Workbooks("before_n_after_remap_audit_umroi.txt").Worksheets("before_n_after_remap_audit_umro").Range("A1:E" & LastRow).Copy _
            Workbooks("UMROI_Standard Cost Audit Reports.xlsm").Worksheets("Before n After Remap Review").Range("A7")


    Workbooks("before_n_after_remap_audit_umroi.txt").Worksheets("before_n_after_remap_audit_umro").Range("F1:G" & LastRow).Copy _
            Workbooks("UMROI_Standard Cost Audit Reports.xlsm").Worksheets("Before n After Remap Review").Range("I7")

End Sub

 

[HazatB]

Not sure exactly what you want by your description but try (untested)...

Sub Test()
    Dim LastRow As Long
    LastRow = Workbooks("before_n_after_remap_audit_umroi.txt").Worksheets("before_n_after_remap_audit_umro").Range("A:G").Find("*", , xlValues, xlPart, xlByRows, xlPrevious, False).Row

    Workbooks("before_n_after_remap_audit_umroi.txt").Worksheets("before_n_after_remap_audit_umro").Range("A1:E" & LastRow).Copy _
            Workbooks("UMROI_Standard Cost Audit Reports.xlsm").Worksheets("Before n After Remap Review").Range("A7")


    Workbooks("before_n_after_remap_audit_umroi.txt").Worksheets("before_n_after_remap_audit_umro").Range("F1:G" & LastRow).Copy _
            Workbooks("UMROI_Standard Cost Audit Reports.xlsm").Worksheets("Before n After Remap Review").Range("I7")

End Sub

This nearly worked for me, to clarify what I need to do is copy data from one workbook that is from a converted text file and paste this information into my master workbook which I was able to figure out. My main goal now is to be able to add or delete the number of rows based on the new data taken from my text file.

 

[MARK858]

Nope, still not sure exactly what you want but as some guesses for the first part of your code (and again untested so expect errors)...

Sub Test2()
    Dim LastRow As Long, x As Long, y As Long
    LastRow = Workbooks("before_n_after_remap_audit_umroi.txt").Worksheets("before_n_after_remap_audit_umro").Range("A:G").Find("*", , xlValues, xlPart, xlByRows, xlPrevious, False).Row
    
    With Workbooks("before_n_after_remap_audit_umroi.txt").Worksheets("before_n_after_remap_audit_umro").Range("A1:E" & LastRow)
        x = .Rows.Count
        y = .Columns.Count
    End With

    Workbooks("UMROI_Standard Cost Audit Reports.xlsm").Worksheets("Before n After Remap Review").Range("A7").Resize(x, y).Delete

End Sub

or

Sub Test3()
    Dim LastRow As Long, x As Long
    LastRow = Workbooks("before_n_after_remap_audit_umroi.txt").Worksheets("before_n_after_remap_audit_umro").Range("A:G").Find("*", , xlValues, xlPart, xlByRows, xlPrevious, False).Row
    
    x = Workbooks("before_n_after_remap_audit_umroi.txt").Worksheets("before_n_after_remap_audit_umro").Range("A1:E" & LastRow).Rows.Count

    Workbooks("UMROI_Standard Cost Audit Reports.xlsm").Worksheets("Before n After Remap Review").Range("A7").Resize(x).EntireRow.Delete

End Sub