CODEWITHSUNDEEP
excelvbadynamicautofill
jhagan05
jhagan05

Reputation: 1

Dynamically fill the next empty row with data from another sheet

In advance, I would like to thank anyone who reads this for taking the time to make any suggestions! I have tried other examples I've found on here and none of them seem to work so thanks for any advice!

So essentially I have 3 sheets. In sheet 1, I will be manually entering data into the next empty row (The data spans from Column A to Column U). Sheet 2 is linked to Sheet 1 in a manner to where if I select a row and autofill down to the next one, it will display the data from Sheet 1 (and also increases the values in each cell to account for inflation).

So essentially after I enter data into a new row on Sheet 1, I want to run a macro that will then dynamically autofill the last row on Sheet 2 to the next empty row. I also want this to be repeated going from Sheet 2 to Sheet 3.

An example would be, if Sheet 1 and 2 both have data down to row 35, I want to be able to manually enter data in row 36 and then my macro will autofill row 35 to 36 on Sheet 2.

The code I have written so far is below. To explain, base/basee and home/homee are cells I have named to compare values from specific columns for my if/then statement. I keep getting Error 1004 on the last line where I try and autofill down to the next cell wit Offset(1,0)

Sub PracticeTool()

   Dim current1 As Integer
   Dim current2 As Integer

     Worksheets("City1").Select
     Application.Goto Reference:="base"
     Selection.End(xlDown).Select
     Selection.End(xlDown).Select

     current1 = Selection

     Worksheets("Inflation").Select
     Application.Goto Reference:="basee"
     Selection.End(xlDown).Select
     Selection.End(xlDown).Select

     current2 = Selection

If (current1 <> current2) Then

    Application.Goto Reference:="homee"
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.AutoFill Destination:=Selection.Offset(1, 0), Type:=xlFillDefault



End If

End Sub

Sheet 1 Sample Data: https://i.sstatic.net/pTFo5.png

Sheet 2 Sample Data: https://i.sstatic.net/kufrV.png

Upvotes: 0

Views: 2189

Answers (1)

Ricardo Diaz
Ricardo Diaz

Reputation: 5696

I didnt't get exactly what you wanted to compare, but I think you're close.

This code should solve the requirement.

Read the comments and adjust it to fit your needs.

Public Sub AutoFillSheets()

    AutoFillRange "Sheet2", "A", "U"
    AutoFillRange "Sheet3", "A", "U"

End Sub


Private Sub AutoFillRange(ByVal targetSheetName As String, ByVal fromColumnLetter As String, toColumnLetter As String)

    Dim targetSheet As Worksheet
    Dim targetRange As Range

    Dim targetLastRow As Long

    Set targetSheet = ThisWorkbook.Worksheets(targetSheetName)

    ' Get the last row in source sheet
    targetLastRow = targetSheet.Cells(targetSheet.Rows.Count, 1).End(xlUp).Row

    ' Set the range to copy
    Set targetRange = targetSheet.Range(fromColumnLetter & targetLastRow & ":" & toColumnLetter & targetLastRow)

    ' You had the error in this line (below). Problem is that to use autofill you need to include the rows from which Excel would calculate the source range (see that I took the last row in the first column, and added one to the second column)
    targetRange.AutoFill Destination:=targetSheet.Range(fromColumnLetter & targetLastRow & ":" & toColumnLetter & targetLastRow + 1)

End Sub

Upvotes: 0

Related Questions