next blank line

[Miree]

I have some code which copies and pastes for a few differnet places how can i
paste from the next blank row

 

[Mike H]

Hi,

This finds the cell below the last entry in column A
Lastrow = Cells(Cells.Rows.Count, "a").End(xlUp).Row + 1

If you don't know hich column the last used row is in then use this
LastRow = UsedRange.Rows.Count + 1

Mike

 

[Miree]

Thats worked thanks, could you maybe also tell me what i would have to do to
then get it to paste from column b not a, i tried just changing the "a" to
"b" but didnt work

 

[Mike H]

Hi,

Lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row + 1

The line of code can go in a worksheet module and then it will return the
last line from that sheet.

If you put it in a general module it will return the last line from the
active sheet.

Change it to this
Lastrow = Sheets("Sheet3").Cells(Cells.Rows.Count, "B").End(xlUp).Row + 1

and it will return sheet3 no matter where you put it.

Mike

 

[Miree]

The first copy and paste is working ok, pasting below my headings, but when i
put a second set of code after it pastes over the exsiting pasted data. Still
neither will paste starting from column B.

 

[Miree]

The first copy and paste i do works(but still strts pasting from column A),
but when i add a second set of code exactlly the same, but a different source
it copys over the first data set instead of going to the next line.

 

[Mike H]

Post your code

The first copy and paste is working ok, pasting below my headings, but when i
put a second set of code after it pastes over the exsiting pasted data. Still
neither will paste starting from column B.

 

[Miree]

' Open the database.
Workbooks.Open "G:\Drilling Fluids Technology\Fluids
Database\FluidDatabaseMM.xls"

'Copy and Paste Section
Windows("FluidDatabaseMM.xls").Activate
Sheets("FormulationsDatabase").Select
Range("A5Z5000").Select
Selection.Copy
Windows("TheDatabase.xls").Activate
Sheets("Formulations").Select
LastRow = Sheets("Formulations").Cells(Cells.Rows.Count,
"B").End(xlUp).row + 1
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Results").Select
Windows("FluidDatabaseMM.xls").Activate
Sheets("ResultsDatabase").Select
Range("A5Z5000").Select
Application.CutCopyMode = False
Selection.Copy
Windows("TheDatabase.xls").Activate
LastRow = Sheets("Results").Cells(Cells.Rows.Count, "B").End(xlUp).row + 1
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


'Close Workbook
Application.DisplayAlerts = False
Workbooks("FluidDatabaseMM.xls").Close
Application.DisplayAlerts = True

The only thing that cahnges is the file the data is copied form

 

[Mike H]

Miree,

You are doing a lot of selecting that you don't need to do. Not tested but
try this

Workbooks.Open "G:\Drilling Fluids Technology\Fluids
Database\FluidDatabaseMM.xls"
Windows("FluidDatabaseMM.xls").Activate
Sheets("FormulationsDatabase").Range("A5Z5000").Copy
Windows("TheDatabase.xls").Activate
Sheets("Formulations").Select
lastrow = Sheets("Formulations").Cells(Cells.Rows.Count,
"B").End(xlUp).Row + 1
Range("B" & lastrow).PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Windows("FluidDatabaseMM.xls").Activate
Sheets("ResultsDatabase").Range("A5Z5000").Copy
Windows("TheDatabase.xls").Activate
lastrow = Sheets("Results").Cells(Cells.Rows.Count, "B").End(xlUp).Row + 1
Range("B" & lastrow).PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'Close Workbook
Application.DisplayAlerts = False
Workbooks("FluidDatabaseMM.xls").Close
Application.DisplayAlerts = True

Mike


Range("B" & lastrow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

 

[Miree]

YAY!! Worked perfectly, thank you very much.

Miree,

You are doing a lot of selecting that you don't need to do. Not tested but
try this

Workbooks.Open "G:\Drilling Fluids Technology\Fluids
Database\FluidDatabaseMM.xls"
Windows("FluidDatabaseMM.xls").Activate
Sheets("FormulationsDatabase").Range("A5Z5000").Copy
Windows("TheDatabase.xls").Activate
Sheets("Formulations").Select
lastrow = Sheets("Formulations").Cells(Cells.Rows.Count,
"B").End(xlUp).Row + 1
Range("B" & lastrow).PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Windows("FluidDatabaseMM.xls").Activate
Sheets("ResultsDatabase").Range("A5Z5000").Copy
Windows("TheDatabase.xls").Activate
lastrow = Sheets("Results").Cells(Cells.Rows.Count, "B").End(xlUp).Row + 1
Range("B" & lastrow).PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'Close Workbook
Application.DisplayAlerts = False
Workbooks("FluidDatabaseMM.xls").Close
Application.DisplayAlerts = True

Mike


Range("B" & lastrow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

 

[Mike H]

Glad I could help

YAY!! Worked perfectly, thank you very much.