VBA how to move cells to bottom of row

[JamesonCreevey]

Hello all,

I have absolutely no knowledge with Excel VBA and was tasked with fixing this Excel sheet at work. I am completely stumped and have tried just about everything so far to get it to work.

I have an import button for other Excel sheets that our emissions team creates for me to import to this specific sheet so we can check data and what not. The import function works and all of my If Then statements seems to work as it will always import into the correct sheet based on the data it has in specific cells etc... But we have about 5-8 different sheets so my boss would like for us to be able to import from any sheet on any line and automatically move the cells to the bottom of the correct sheet. The last part is where I am struggling, I can't seem to move the cells to the bottom. If I import from line 7 in a different sheet it will import to the correct sheet, just at line 7 instead of the bottom which causes a lot of issues. I know it seems like a lot but I'm sure it's a simple fix. Here is the code I used to try to fix this issue.

Dim row As Variant
row = 6

Do While Cells(row, 1).Value <> ""
Cells(row, 1).Select
row = row + 1

Loop
Cells(row, 1).Select

I've been searching for days and have tried just about everything but it still doesn't seem to work. So that's why I'm here, any help would be greatly appreciated as I am about to rip my hair out from this.

P.S sorry if this has been asked a million times, I tried searching for an answer but couldn't seem to find one.

 

[mumps]

I'm not sure if this will help but you don't have to loop to find the first available row at the bottom of the sheet. This code copies cell A1 from Sheet1 to the first blank row in column A of Sheet2:

Sheets("Sheet1").Range("A1").Copy Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "A").End(xlUp).Offset(1)

 

[JamesonCreevey]

I'm not sure if this will help but you don't have to loop to find the first available row at the bottom of the sheet. This code copies cell A1 from Sheet1 to the first blank row in column A of Sheet2:

Sheets("Sheet1").Range("A1").Copy Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "A").End(xlUp).Offset(1)

Thanks, I will give it a try! I appreciate the help