CODEWITHSUNDEEP
excelvba
NeverBrave
NeverBrave

Reputation: 15

How to paste to the last filled row without including first row (headers)?

I'm trying to paste data at the end of the last filled row in a sheet, but my syntax overwrites my headers in Row 1 during the first iteration of the loop.

I'm running a looped code that copies and transposes variables from multiple spreadsheets on a single sheet. Variables are in two columns in the source files but go onto one row in the destination file. I use a "next row" function to place the data from the first column into the first empty row. I then want to use a "last row" function to append the data from the second column to the same row. However, during the first operation, my code doesn't know the difference between the first line of data and the variable names on the first row, so the second column data ends up shifted one row up from the first half of the data.

Dim lastrow As Long
Dim nextrow As Long

Set sht = ThisWorkbook.Worksheets("Sheet1")

lastrow = sht.Cells(sht.Rows.Count, 1).End(xlUp).Row
nextrow = sht.Cells(sht.Rows.Count, 1).End(xlUp).Row + 1
    'assigns objects for the last filled row and the next unfilled row


wb1.Worksheets("Database").Range("B1:B578").Copy
Workbooks("zzmaster.xlsx").Worksheets("Sheet1").Cells(nextrow, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
DoEvents
Application.CutCopyMode = False
    'copies the relevant data from Column B in Sheet 3 of the source file...
    'then transposes and pastes into the next available row of the destination file


wb1.Worksheets("Database").Range("C32:C578").Copy
Workbooks("zzmaster.xlsx").Worksheets("Sheet1").Cells(lastrow, 579).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
DoEvents
Application.CutCopyMode = False
    'copies the relevant data from Column C in Sheet 3 of the source file...
    'then tranposes and pastes into the same row as the previous function, beginning with...
    'the next cell after the last data point

Is there a good way to adjust the syntax so that data does not paste to the first row of the spreadsheet? The goal is to have both columns from a source spreadsheet be pasted to the same row, and that each spreadsheet in the loop be pasted to the next empty row available. I appreciate any suggestions.

Upvotes: 0

Views: 663

Answers (2)

T4roy
T4roy

Reputation: 196

I think you want to change the cell you are pasting to, you don't want to paste in lastrow it should be nextrow.

You have;

Workbooks("zzmaster.xlsx").Worksheets("Sheet1").Cells(lastrow, 579).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

Change to;

Workbooks("zzmaster.xlsx").Worksheets("Sheet1").Cells(nextrow, 579).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

Looks like it wouldn't only be the first iteration of your loop but all of them. Try adding a breakpoint where you set lastrow and step through the execution to see where the issues arises, it will help to step through a few iterations of your loop and assess if it only happens once or not.

Upvotes: 1

Lee Li Fong
Lee Li Fong

Reputation: 259

Assuming your header is at row 1, can you add coding after this:

lastrow = sht.Cells(sht.Rows.Count, 1).End(xlUp).Row

If lastrow=1 then lastrow=2

Upvotes: 0

Related Questions