| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
next blank line
I have some code which copies and pastes for a few differnet places how can i
paste from the next blank row |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
next blank line
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" wrote: I have some code which copies and pastes for a few differnet places how can i paste from the next blank row |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
next blank line
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" wrote: 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" wrote: I have some code which copies and pastes for a few differnet places how can i paste from the next blank row |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
next blank line
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" wrote: 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" wrote: 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" wrote: I have some code which copies and pastes for a few differnet places how can i paste from the next blank row |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
next blank line
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. "Mike H" wrote: 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" wrote: 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" wrote: 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" wrote: I have some code which copies and pastes for a few differnet places how can i paste from the next blank row |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
next blank line
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" wrote: 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" wrote: I have some code which copies and pastes for a few differnet places how can i paste from the next blank row |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
next blank line
Post your code
"Miree" wrote: 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. "Mike H" wrote: 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" wrote: 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" wrote: 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" wrote: I have some code which copies and pastes for a few differnet places how can i paste from the next blank row |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
next blank line
' 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("A5:DZ5000").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("A5:DZ5000").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" wrote: Post your code "Miree" wrote: 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. "Mike H" wrote: 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" wrote: 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" wrote: 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" wrote: I have some code which copies and pastes for a few differnet places how can i paste from the next blank row |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
next blank line
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("A5:DZ5000"). 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("A5:DZ5000").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" wrote: ' 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("A5:DZ5000").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("A5:DZ5000").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" wrote: Post your code "Miree" wrote: 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. "Mike H" wrote: 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" wrote: 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" wrote: 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" wrote: I have some code which copies and pastes for a few differnet places how can i paste from the next blank row |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
next blank line
YAY!! Worked perfectly, thank you very much.
"Mike H" wrote: 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("A5:DZ5000"). 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("A5:DZ5000").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" wrote: ' 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("A5:DZ5000").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("A5:DZ5000").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" wrote: Post your code "Miree" wrote: 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. "Mike H" wrote: 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" wrote: 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" wrote: 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" wrote: I have some code which copies and pastes for a few differnet places how can i paste from the next blank row |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
next blank line
Glad I could help
"Miree" wrote: YAY!! Worked perfectly, thank you very much. "Mike H" wrote: 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("A5:DZ5000"). 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("A5:DZ5000").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" wrote: ' 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("A5:DZ5000").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("A5:DZ5000").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" wrote: Post your code "Miree" wrote: 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. "Mike H" wrote: 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" wrote: 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" wrote: 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" wrote: I have some code which copies and pastes for a few differnet places how can i paste from the next blank row |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find Blank Line | Excel Programming | |||
| How do you add a blank line automatically after the Subtotal line | Excel Worksheet Functions | |||
| Insert Blank Line | Excel Discussion (Misc queries) | |||
| If cell is not blank, then line out row | Excel Worksheet Functions | |||
| Next blank line | Excel Programming | |||
Linear Mode
