CODEWITHSUNDEEP
vbaexcel
Hermes Omar Gomez
Hermes Omar Gomez

Reputation: 67

Paste data on last row of input sheet

Im creating a macro which pulls data from several columns on several sheets and organize all data into one sheet.

The code i have is :

Private Sub Update_Click()

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim path As String, fileName As String
    Dim lastRowInput As Long, lastRowOutput As Long, rowCntr As Long, lastColumn As Long
    Dim inputWS1 As Worksheet, inputWS2 As Worksheet, outputWS As Worksheet

    'set your sheets here
    Set inputWS1 = ThisWorkbook.Sheets("Universal")
    Set inputWS2 = ThisWorkbook.Sheets("Geovera")
    Set outputWS = ThisWorkbook.Sheets("Carriers")


    rowCntr = 1

    'get last rows from both sheets
    lastRowUniversal = inputWS1.Cells(Rows.Count, "A").End(xlUp).Row
    lastRowGeovera = inputWS2.Cells(Rows.Count, "F").End(xlUp).Row
    LastRowPolicy = outputWS.Cells(Rows.Count, "B").End(xlUp).Row + 1
    lastRowOutput = outputWS.Cells(Rows.Count, "A").End(xlUp).Row
    lastColumn = inputWS1.Cells(1, Columns.Count).End(xlToLeft).Column

    'Universal
    inputWS1.Range("A4:A" & lastRowUniversal).Copy outputWS.Range("B2")
    inputWS1.Range("B4:B" & lastRowUniversal).Copy outputWS.Range("C2")
    outputWS.Range("E2:E" & (lastRowUniversal - 2)).Value = inputWS1.Name
    inputWS1.Range("J4:J" & lastRowUniversal).Copy outputWS.Range("G2")
    outputWS.Range("G2:G" & (lastRowUniversal - 2)).Value = Evaluate("=IF(ISNUMBER(G2:G" & (lastRowUniversal - 2) & "),DATE(YEAR(G2:G" & (lastRowUniversal - 2) & ")-1,MONTH(G2:G" & (lastRowUniversal - 2) & "),DAY(G2:G" & lastRowUniversal & ")),G2:G" & (lastRowUniversal - 2) & ")")
    inputWS1.Range("G4:G" & lastRowUniversal).Copy outputWS.Range("H2")

    'Geovera
    inputWS2.Range("F2:F" & lastRowOutput).Copy outputWS.Range("B65536").End(xlUp).Row

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic 
End Sub

I finished working on migrating one sheet to the organized main sheet.

When i start setting up the 2nd sheet i noticed that it overwrites the data from the first sheet. I want a code that will paste the data at the first blank space on the row.

My error is coming at this line:

inputWS2.Range("F2:F" & lastRowOutput).Copy outputWS.Range("B65536").End(xlUp).Row

is still not there. But i want the data from column F to move to column B on the main sheet without deleting what i already copied on the previous sheet.

Please help

Upvotes: 0

Views: 192

Answers (1)

ShanayL
ShanayL

Reputation: 1247

instead of 

inputWS2.Range("F2:F" & lastRowOutput).Copy outputWS.Range("B65536").End(xlUp).Row

try 

inputWS2.Range("F2:F" & lastRowGeovera).Copy outputWS.Range("B" & lastRowUniversal - 1)

Upvotes: 1

Related Questions