| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro - Next blank row
I have A summary Sheet, and a few tabs, and i want a macro to collate
all this information. So far i have; -------------------------- Dim rng As Range Set rng = Sheets("Summary Sheet").Range("A65536").End(xlUp).Offset(1, 0) Sheets("SME Quarterly").Select Rows("15:43").Select Selection.Copy Destination:=rng Application.CutCopyMode = False Sheets("Sheet1").Select Rows("15:43").Select Selection.Copy Destination:=rng Application.CutCopyMode = False Sheets("Sheet2").Select Rows("15:43").Select Selection.Copy Destination:=rng Application.CutCopyMode = False Sheets("Sheet3").Select Rows("15:43").Select Selection.Copy Destination:=rng Application.CutCopyMode = False End Sub -------------------------- All it seems to do is paste over the top of each entry. Thanks if you can help. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro - Next blank row
Maybe this way.
Note sheets aren't selected, for this operation there' no need Sub copy() Sheets("SME Quarterly").Rows("15:43").copy lastrow = Sheets("Summary Sheet").Cells(Rows.Count, "A").End(xlUp).Row Sheets("Summary Sheet").Range("A" & lastrow + 1).PasteSpecial Application.CutCopyMode = False Sheets("Sheet1").Rows("15:43").copy lastrow = Sheets("Summary Sheet").Cells(Rows.Count, "A").End(xlUp).Row Sheets("Summary Sheet").Range("A" & lastrow + 1).PasteSpecial Application.CutCopyMode = False Sheets("Sheet2").Rows("15:43").copy lastrow = Sheets("Summary Sheet").Cells(Rows.Count, "A").End(xlUp).Row Sheets("Summary Sheet").Range("A" & lastrow + 1).PasteSpecial Application.CutCopyMode = False Sheets("Sheet3").Rows("15:43").copy lastrow = Sheets("Summary Sheet").Cells(Rows.Count, "A").End(xlUp).Row Sheets("Summary Sheet").Range("A" & lastrow + 1).PasteSpecial Application.CutCopyMode = False End Sub Mike "NPell" wrote: I have A summary Sheet, and a few tabs, and i want a macro to collate all this information. So far i have; -------------------------- Dim rng As Range Set rng = Sheets("Summary Sheet").Range("A65536").End(xlUp).Offset(1, 0) Sheets("SME Quarterly").Select Rows("15:43").Select Selection.Copy Destination:=rng Application.CutCopyMode = False Sheets("Sheet1").Select Rows("15:43").Select Selection.Copy Destination:=rng Application.CutCopyMode = False Sheets("Sheet2").Select Rows("15:43").Select Selection.Copy Destination:=rng Application.CutCopyMode = False Sheets("Sheet3").Select Rows("15:43").Select Selection.Copy Destination:=rng Application.CutCopyMode = False End Sub -------------------------- All it seems to do is paste over the top of each entry. Thanks if you can help. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro - Next blank row
On 22 Aug, 15:35, Mike H wrote:
Maybe this way. Note sheets aren't selected, for this operation there' no need Sub copy() * * Sheets("SME Quarterly").Rows("15:43").copy * * lastrow = Sheets("Summary Sheet").Cells(Rows.Count, "A").End(xlUp).Row * * Sheets("Summary Sheet").Range("A" & lastrow + 1).PasteSpecial * * Application.CutCopyMode = False * * Sheets("Sheet1").Rows("15:43").copy * * lastrow = Sheets("Summary Sheet").Cells(Rows.Count, "A").End(xlUp).Row * * Sheets("Summary Sheet").Range("A" & lastrow + 1).PasteSpecial * * Application.CutCopyMode = False * * Sheets("Sheet2").Rows("15:43").copy * * lastrow = Sheets("Summary Sheet").Cells(Rows.Count, "A").End(xlUp).Row * * Sheets("Summary Sheet").Range("A" & lastrow + 1).PasteSpecial * * Application.CutCopyMode = False * * Sheets("Sheet3").Rows("15:43").copy * * lastrow = Sheets("Summary Sheet").Cells(Rows.Count, "A").End(xlUp).Row * * Sheets("Summary Sheet").Range("A" & lastrow + 1).PasteSpecial * * Application.CutCopyMode = False End Sub Mike "NPell" wrote: I have A summary Sheet, and a few tabs, and i want a macro to collate all this information. So far i have; -------------------------- * * Dim rng As Range * * Set rng = Sheets("Summary Sheet").Range("A65536").End(xlUp).Offset(1, 0) * * Sheets("SME Quarterly").Select * * Rows("15:43").Select * * Selection.Copy Destination:=rng * * Application.CutCopyMode = False * * Sheets("Sheet1").Select * * Rows("15:43").Select * * Selection.Copy Destination:=rng * * Application.CutCopyMode = False * * Sheets("Sheet2").Select * * Rows("15:43").Select * * Selection.Copy Destination:=rng * * Application.CutCopyMode = False * * Sheets("Sheet3").Select * * Rows("15:43").Select * * Selection.Copy Destination:=rng * * Application.CutCopyMode = False End Sub -------------------------- All it seems to do is paste over the top of each entry. Thanks if you can help.- Hide quoted text - - Show quoted text - Thanks Mike! It works!! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro - Next blank row
Excellent, Glad I could help
"NPell" wrote: On 22 Aug, 15:35, Mike H wrote: Maybe this way. Note sheets aren't selected, for this operation there' no need Sub copy() Sheets("SME Quarterly").Rows("15:43").copy lastrow = Sheets("Summary Sheet").Cells(Rows.Count, "A").End(xlUp).Row Sheets("Summary Sheet").Range("A" & lastrow + 1).PasteSpecial Application.CutCopyMode = False Sheets("Sheet1").Rows("15:43").copy lastrow = Sheets("Summary Sheet").Cells(Rows.Count, "A").End(xlUp).Row Sheets("Summary Sheet").Range("A" & lastrow + 1).PasteSpecial Application.CutCopyMode = False Sheets("Sheet2").Rows("15:43").copy lastrow = Sheets("Summary Sheet").Cells(Rows.Count, "A").End(xlUp).Row Sheets("Summary Sheet").Range("A" & lastrow + 1).PasteSpecial Application.CutCopyMode = False Sheets("Sheet3").Rows("15:43").copy lastrow = Sheets("Summary Sheet").Cells(Rows.Count, "A").End(xlUp).Row Sheets("Summary Sheet").Range("A" & lastrow + 1).PasteSpecial Application.CutCopyMode = False End Sub Mike "NPell" wrote: I have A summary Sheet, and a few tabs, and i want a macro to collate all this information. So far i have; -------------------------- Dim rng As Range Set rng = Sheets("Summary Sheet").Range("A65536").End(xlUp).Offset(1, 0) Sheets("SME Quarterly").Select Rows("15:43").Select Selection.Copy Destination:=rng Application.CutCopyMode = False Sheets("Sheet1").Select Rows("15:43").Select Selection.Copy Destination:=rng Application.CutCopyMode = False Sheets("Sheet2").Select Rows("15:43").Select Selection.Copy Destination:=rng Application.CutCopyMode = False Sheets("Sheet3").Select Rows("15:43").Select Selection.Copy Destination:=rng Application.CutCopyMode = False End Sub -------------------------- All it seems to do is paste over the top of each entry. Thanks if you can help.- Hide quoted text - - Show quoted text - Thanks Mike! It works!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to AddRow if Blank and Autosum | Excel Discussion (Misc queries) | |||
| Macro not recognizing blank lines as blank | Excel Discussion (Misc queries) | |||
| Macro to hide the row if both columns E & F are blank in that row. | Excel Worksheet Functions | |||
| Macro To Insert Blank Row? | Excel Discussion (Misc queries) | |||
| Using a Macro to paste into Blank Cells | Excel Worksheet Functions | |||
Linear Mode
