| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste into next blank row in a different worksheet
I need to copy a row of data from worksheet1 to the next available row in
worksheet2. Worksheet1 cells (A2:K2) display results from a 'LOOKUP' function and cells (L2:AB2) in worksheet1 utilize the 'IF' function to display results from worksheet3. I only want the results from the LOOKUP and IF functions to populate the next available row in worksheet2. I'd like to utilize the 'CommandButton' that I've programed witha macro to clear the contents of worksheet3 to initialize or complete the transfer of data from worksheet1 to worksheet2. Or will that happen automatically when I clear the data from worksheet3? -- dhunter43 The Performance Advantage, LLC |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Paste into next blank row in a different worksheet
You have to clear sheet3. Hre is some code that may help
Sub Macro2() ' ' Macro2 Macro ' Macro recorded 7/25/2007 ' ' Sheets("Sheet1").Select Rows("20:20").Select Selection.Copy Sheets("Sheet2").Select LastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1 Rows(LastRow & ":" & LastRow).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Sheet3").Range("B5").EntireRow.ClearConten ts End Sub "dhunter43" wrote: I need to copy a row of data from worksheet1 to the next available row in worksheet2. Worksheet1 cells (A2:K2) display results from a 'LOOKUP' function and cells (L2:AB2) in worksheet1 utilize the 'IF' function to display results from worksheet3. I only want the results from the LOOKUP and IF functions to populate the next available row in worksheet2. I'd like to utilize the 'CommandButton' that I've programed witha macro to clear the contents of worksheet3 to initialize or complete the transfer of data from worksheet1 to worksheet2. Or will that happen automatically when I clear the data from worksheet3? -- dhunter43 The Performance Advantage, LLC |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Paste into next blank row in a different worksheet
I am receiving a "Compile Error: Syntax error" when I try to run this macro.
The hilighted copy is Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, I associated the macro with the 'CommandButton' I placed in worksheet3, is that the right place for it? I appreciate your assistance. I need to log off in a few minutes to get to work but will look for your response when I get home tonight. Thank you, -- dhunter43 The Performance Advantage, LLC "Joel" wrote: You have to clear sheet3. Hre is some code that may help Sub Macro2() ' ' Macro2 Macro ' Macro recorded 7/25/2007 ' ' Sheets("Sheet1").Select Rows("20:20").Select Selection.Copy Sheets("Sheet2").Select LastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1 Rows(LastRow & ":" & LastRow).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Sheet3").Range("B5").EntireRow.ClearConten ts End Sub "dhunter43" wrote: I need to copy a row of data from worksheet1 to the next available row in worksheet2. Worksheet1 cells (A2:K2) display results from a 'LOOKUP' function and cells (L2:AB2) in worksheet1 utilize the 'IF' function to display results from worksheet3. I only want the results from the LOOKUP and IF functions to populate the next available row in worksheet2. I'd like to utilize the 'CommandButton' that I've programed witha macro to clear the contents of worksheet3 to initialize or complete the transfer of data from worksheet1 to worksheet2. Or will that happen automatically when I clear the data from worksheet3? -- dhunter43 The Performance Advantage, LLC |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Paste into next blank row in a different worksheet
The proble has tto due with line wrapping. When code is pasted on this
website the length is too long and then when it is pasted in VBA it gives an error Sub Macro2() ' ' Macro2 Macro ' Macro recorded 7/25/2007 ' ' Sheets("Sheet1").Select Rows("20:20").Select Selection.Copy Sheets("Sheet2").Select LastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1 Rows(LastRow & ":" & LastRow).Select Selection.PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Sheet3").Range("B5").EntireRow.ClearConten ts End Sub "dhunter43" wrote: I am receiving a "Compile Error: Syntax error" when I try to run this macro. The hilighted copy is Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, I associated the macro with the 'CommandButton' I placed in worksheet3, is that the right place for it? I appreciate your assistance. I need to log off in a few minutes to get to work but will look for your response when I get home tonight. Thank you, -- dhunter43 The Performance Advantage, LLC "Joel" wrote: You have to clear sheet3. Hre is some code that may help Sub Macro2() ' ' Macro2 Macro ' Macro recorded 7/25/2007 ' ' Sheets("Sheet1").Select Rows("20:20").Select Selection.Copy Sheets("Sheet2").Select LastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1 Rows(LastRow & ":" & LastRow).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Sheet3").Range("B5").EntireRow.ClearConten ts End Sub "dhunter43" wrote: I need to copy a row of data from worksheet1 to the next available row in worksheet2. Worksheet1 cells (A2:K2) display results from a 'LOOKUP' function and cells (L2:AB2) in worksheet1 utilize the 'IF' function to display results from worksheet3. I only want the results from the LOOKUP and IF functions to populate the next available row in worksheet2. I'd like to utilize the 'CommandButton' that I've programed witha macro to clear the contents of worksheet3 to initialize or complete the transfer of data from worksheet1 to worksheet2. Or will that happen automatically when I clear the data from worksheet3? -- dhunter43 The Performance Advantage, LLC |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Paste into next blank row in a different worksheet
For some reason this line of code only copies to row19 even though there are
blank rows above it. It would be ok if it pasted data to row20 next and so on but all data is pasted to row19 over and over again. LastRow = Cells(Rows.Count, "a").End(xlUp).Row + 1 ActiveSheet.Rows(LastRow & ":" & LastRow).Select I cannot figure out where the problem is. Thank you -- dhunter43 The Performance Advantage, LLC "Joel" wrote: The proble has tto due with line wrapping. When code is pasted on this website the length is too long and then when it is pasted in VBA it gives an error Sub Macro2() ' ' Macro2 Macro ' Macro recorded 7/25/2007 ' ' Sheets("Sheet1").Select Rows("20:20").Select Selection.Copy Sheets("Sheet2").Select LastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1 Rows(LastRow & ":" & LastRow).Select Selection.PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Sheet3").Range("B5").EntireRow.ClearConten ts End Sub "dhunter43" wrote: I am receiving a "Compile Error: Syntax error" when I try to run this macro. The hilighted copy is Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, I associated the macro with the 'CommandButton' I placed in worksheet3, is that the right place for it? I appreciate your assistance. I need to log off in a few minutes to get to work but will look for your response when I get home tonight. Thank you, -- dhunter43 The Performance Advantage, LLC "Joel" wrote: You have to clear sheet3. Hre is some code that may help Sub Macro2() ' ' Macro2 Macro ' Macro recorded 7/25/2007 ' ' Sheets("Sheet1").Select Rows("20:20").Select Selection.Copy Sheets("Sheet2").Select LastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1 Rows(LastRow & ":" & LastRow).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Sheet3").Range("B5").EntireRow.ClearConten ts End Sub "dhunter43" wrote: I need to copy a row of data from worksheet1 to the next available row in worksheet2. Worksheet1 cells (A2:K2) display results from a 'LOOKUP' function and cells (L2:AB2) in worksheet1 utilize the 'IF' function to display results from worksheet3. I only want the results from the LOOKUP and IF functions to populate the next available row in worksheet2. I'd like to utilize the 'CommandButton' that I've programed witha macro to clear the contents of worksheet3 to initialize or complete the transfer of data from worksheet1 to worksheet2. Or will that happen automatically when I clear the data from worksheet3? -- dhunter43 The Performance Advantage, LLC |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Paste into next blank row in a different worksheet
Try this. the code was using the selected range on sheet one for pasting the
cells on sheett 2. Sub Macro2() ' ' Macro2 Macro ' Macro recorded 7/25/2007 ' ' Sheets("Sheet1").Rows("20:20").Select Selection.Copy LastRow = Sheets("Sheet2"). _ Cells(Rows.Count, "A").End(xlUp).Row + 1 Sheets("Sheet2").Rows(LastRow & ":" & LastRow).Select Selection.PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Sheet3").Range("B5").EntireRow.ClearConten ts End Sub "dhunter43" wrote: For some reason this line of code only copies to row19 even though there are blank rows above it. It would be ok if it pasted data to row20 next and so on but all data is pasted to row19 over and over again. LastRow = Cells(Rows.Count, "a").End(xlUp).Row + 1 ActiveSheet.Rows(LastRow & ":" & LastRow).Select I cannot figure out where the problem is. Thank you -- dhunter43 The Performance Advantage, LLC "Joel" wrote: The proble has tto due with line wrapping. When code is pasted on this website the length is too long and then when it is pasted in VBA it gives an error Sub Macro2() ' ' Macro2 Macro ' Macro recorded 7/25/2007 ' ' Sheets("Sheet1").Select Rows("20:20").Select Selection.Copy Sheets("Sheet2").Select LastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1 Rows(LastRow & ":" & LastRow).Select Selection.PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Sheet3").Range("B5").EntireRow.ClearConten ts End Sub "dhunter43" wrote: I am receiving a "Compile Error: Syntax error" when I try to run this macro. The hilighted copy is Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, I associated the macro with the 'CommandButton' I placed in worksheet3, is that the right place for it? I appreciate your assistance. I need to log off in a few minutes to get to work but will look for your response when I get home tonight. Thank you, -- dhunter43 The Performance Advantage, LLC "Joel" wrote: You have to clear sheet3. Hre is some code that may help Sub Macro2() ' ' Macro2 Macro ' Macro recorded 7/25/2007 ' ' Sheets("Sheet1").Select Rows("20:20").Select Selection.Copy Sheets("Sheet2").Select LastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1 Rows(LastRow & ":" & LastRow).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Sheet3").Range("B5").EntireRow.ClearConten ts End Sub "dhunter43" wrote: I need to copy a row of data from worksheet1 to the next available row in worksheet2. Worksheet1 cells (A2:K2) display results from a 'LOOKUP' function and cells (L2:AB2) in worksheet1 utilize the 'IF' function to display results from worksheet3. I only want the results from the LOOKUP and IF functions to populate the next available row in worksheet2. I'd like to utilize the 'CommandButton' that I've programed witha macro to clear the contents of worksheet3 to initialize or complete the transfer of data from worksheet1 to worksheet2. Or will that happen automatically when I clear the data from worksheet3? -- dhunter43 The Performance Advantage, LLC |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need macro to check if cell is not blank & previous cell is blank, copy information from row above & paste | Excel Programming | |||
| Macro copy and paste = blank worksheet | Excel Discussion (Misc queries) | |||
| How to find first blank row to paste to? | Excel Programming | |||
| I want a blank space on a worksheet when the question is blank? | Excel Discussion (Misc queries) | |||
| Copying and pasting a worksheet to a blank and removing blank rows | Excel Programming | |||
Hybrid Mode
