VBA Question: How to adjust for rows when copying.

[HazatB]

Hello,

I am attempting to create a macro that deletes a wide range of data in a table and copies and pastes new information in to that area that is taken from a source file. However, I am having trouble adjusting both more and less data. I first figured out various test with the lastrow/Range. option but all I could get is a count of rows, I have added another prompt that was more successful as it adding/deleted rows however I had a problem keeping it from deleting the last line which would have the formulas summing the numbers. Could anyone please assists with figuring out this problem?

 

[My Aswer Is This]

I see your new to this forum. When asking for help here we always need specific details none of which you provided.

You said:
1. that deletes a wide range of data in a table. My question: "What data" What Table
2. and copies and pastes new information in to that area that is taken from a source file. My question copies what data and paste into what area? And taken from what source file?

 

[HazatB]

I see your new to this forum. When asking for help here we always need specific details none of which you provided.

You said:
1. that deletes a wide range of data in a table. My question: "What data" What Table
2. and copies and pastes new information in to that area that is taken from a source file. My question copies what data and paste into what area? And taken from what source file?

Sorry for not having enough information. The Table contains records of individual information and their financial numbers, it has headers, two columns that calculate the totals and sums it up at the bottom. When running my macro I delete the data which goes from cell A7 to E7 down to cell A6675 to E6675. Once the data is deleted it navigates to files and selects a text file which is then converted as tab delimitated. The excel version of the text file contains the new data for this month that will replace the one i deleted for the prior month.

The problem that I am having is adjusting for the number of rows in my macro to add/delete rows without deleting the bottom line summing the totals.

 

[HazatB]

This is an example of the excel sheet.

(Header Row A7 to E7) Client Information - Database Information - Financial Data
(Data Row A8 to E6675) Micheal Lewis - North township - 889,765.39
(Sum Row A6676 to E6676) 505,531,915.65

 

[JackDanIce]

Why don't you just recreate the formula row? Try adjusting:

Sub SumRow()

    Dim strSum  As String
    Dim LR      As Long
    
    strSum = "=SUM($A$8:$A$@LR)"
    LR = Cells(Rows.count, 1).End(xlUp).row + 1
    Cells(LR, 1).Resize(, 5).Formula = Replace(strSum, "@LR", LR)

End Sub

It may be helpful to post the code you already have.

 

[HazatB]

I will post the code when I get in the office after my commute

 

[HazatB]

This is my code

Dim myLastRow As Long

myLastRow = Worksheets("Before n After Remap Review").Range("A7").End(xlDown).Row

MsgBox prompt:="The last row is Row No. " & myLastRow

Application.ScreenUpdating = False
For a = 7 To 6657 'Or however many rows u have
On Error GoTo Err_Execute
Sheets("before_n_after_remap_audit_umro").Rows(a).Copy
Sheets("Before n After Remap Review").Rows(a).Insert Shift:=xlDown
Next a
Application.ScreenUpdating = True
Err_Execute:
If Err.Number = 0 Then MsgBox "All have been copied!" Else _
MsgBox Err.Description

 

[HazatB]

Why don't you just recreate the formula row? Try adjusting:

Sub SumRow()

    Dim strSum  As String
    Dim LR      As Long
    
    strSum = "=SUM($A$8:$A$@LR)"
    LR = Cells(Rows.count, 1).End(xlUp).row + 1
    Cells(LR, 1).Resize(, 5).Formula = Replace(strSum, "@LR", LR)

End Sub

It may be helpful to post the code you already have.

I tried this and it worked I just need to test when I have more/less rows

 

[JackDanIce]

Try:

Sub Macro()

    Dim LR      As Long
    Dim x       As Long
    Dim wksS    As Worksheet
    Dim wksD    As Worksheet
    
    Set wksD = Sheets("Before n After Remap Review")
    Set wksS = Sheets("before_n_after_remap_audit_umro")
    
    LR = wksD.Cells(Rows.count, 1).End(xlUp).row
    LR = Application.Max(7, LR)
    
    Application.ScreenUpdating = False
        
    For x = 7 To LR
        wksS.Rows(x).Copy
        wksD.Rows(x).Insert shift:=xlDown
    Next x
    
    LR = wksD.Cells(Rows.count, 1).End(xlUp).row + 1
    wksD.Cells(1, LR).Formula = "=SUM($A$7:$A$" & LR - 1 & ")"
    
    With Application
        .ScreenUpdating = True
        .CutCopyMode False
    End With
    
    Set wksS = Nothing
    Set wksD = Nothing
    
End Sub

I'm not sure why you're inserting rows and then copying it, why don't you just clear all the data on Sheets("Before n After Remap Review"), then copy all the data from Sheets("before_n_after_remap_audit_umro" and then add the sum formula.

Your method could be faster than repeating insert and copy actions inside a loop.

 

[HazatB]

Try:

Sub Macro()

    Dim LR      As Long
    Dim x       As Long
    Dim wksS    As Worksheet
    Dim wksD    As Worksheet
    
    Set wksD = Sheets("Before n After Remap Review")
    Set wksS = Sheets("before_n_after_remap_audit_umro")
    
    LR = wksD.Cells(Rows.count, 1).End(xlUp).row
    LR = Application.Max(7, LR)
    
    Application.ScreenUpdating = False
        
    For x = 7 To LR
        wksS.Rows(x).Copy
        wksD.Rows(x).Insert shift:=xlDown
    Next x
    
    LR = wksD.Cells(Rows.count, 1).End(xlUp).row + 1
    wksD.Cells(1, LR).Formula = "=SUM($A$7:$A$" & LR - 1 & ")"
    
    With Application
        .ScreenUpdating = True
        .CutCopyMode False
    End With
    
    Set wksS = Nothing
    Set wksD = Nothing
    
End Sub

I'm not sure why you're inserting rows and then copying it, why don't you just clear all the data on Sheets("Before n After Remap Review"), then copy all the data from Sheets("before_n_after_remap_audit_umro" and then add the sum formula.

Your method could be faster than repeating insert and copy actions inside a loop.

Yeah I understand I was just seeing if when copying & pasting I could adjust rows but I think starting with the code you posted I could achieve my desired results