How do I delete profile 2.0 and change it back to 1.0?

Visual basic for macros

• QUESTION: I have two spreadsheets open, Lets call them A and B. I want to copy and paste 10 rows from A into B. The problem that I am having with my coding is that I don't want to copy and paste the same rows from A into B everytime. Rather, I want to copy10 rows down from the currently selected row in A and then paste them in B. And after they have been pasted in B, return to A and select the 11th row so that the next time the macro is run it goes in a copy/paste sequence of 10 rows at a time. here is what the coding currently looks like for this operation: Rows("1561:1570").Select Selection.Copy Windows("data measurement_09-09-08_0630.lvm").Activate Application.CutCopyMode = False ActiveSheet.Paste ANSWER: Ben This worked for me as I understand you description: Sub ABC() Dim bk1 As Workbook, sh1 As Worksheet Dim sh As Worksheet, rw As Long, r As Range Set bk1 = Workbooks("data measurement_09-09-08_0630.lvm") Set sh1 = bk1.ActiveSheet Set sh = ActiveSheet rw = sh1.Cells(sh1.Rows.Count, 1).End(xlUp).Row + 1 Set r = sh.Cells(ActiveCell.Row, 1) r.Resize(10, 1).EntireRow.Copy sh1.Rows(rw) r.Offset(10, 0).Select Application.CutCopyMode = False End Sub The code is a little longer, but is much more efficient and controllable. ---------- FOLLOW-UP ---------- QUESTION: Thank you so much for the response, this could save me days worth of work. The Coding above is working but needs a little tweaking so if you could help me a bit more I would really appreciate it. The line that reads: Set bk1 = Workbooks("data measurement_09-09-08_0630.lvm") I dont think this will work because this workbook will be different everytime the macro is run, so there is an error if i choose a workbook with a different time stamp. The book that remains constant is Windows("2009-Sep Weather Data.csv"). This is the book that I referred to as book A in my explanation. I could change this in the coding manually when processing for different months if need be. The other problem is the location of the paste. I need the 10 rows from Windows("2009-Sep Weather Data.csv"). to be pasted into cell AW1 of Workbooks("data measurement_given_date_time.lvm") let me know if you need any more explanation and again thank you soo much for taking the time to help. p.s. below is the entire code sequence as it stands now including your input: Sub xx00_macro() ' ' xx00_macro Macro ' Macro recorded 9/29/2010 by Ben Schams ' ' Keyboard Shortcut: Option+Cmd+b ' Rows("2:600").Select Selection.Delete Shift:=xlUp Rows("3:601").Select Selection.Delete Shift:=xlUp Rows("4:602").Select Selection.Delete Shift:=xlUp Rows("5:603").Select Selection.Delete Shift:=xlUp Rows("6:604").Select Selection.Delete Shift:=xlUp Rows("7:605").Select Selection.Delete Shift:=xlUp Rows("8:606").Select Selection.Delete Shift:=xlUp Rows("9:607").Select Selection.Delete Shift:=xlUp Rows("10:608").Select Selection.Delete Shift:=xlUp Rows("11:609").Select Selection.Delete Shift:=xlUp Range("AW1").Select Windows("2009-Sep Weather Data.csv").Activate ActiveWindow.SmallScroll Down:=10 Dim bk1 As Workbook, sh1 As Worksheet Dim sh As Worksheet, rw As Long, r As Range Set bk1 = Workbooks("data measurement_09-09-08_0630.lvm") Set sh1 = bk1.ActiveSheet Set sh = ActiveSheet rw = sh1.Cells(sh1.Rows.Count, 1).End(xlUp).Row + 1 Set r = sh.Cells(ActiveCell.Row, 1) r.Resize(10, 1).EntireRow.Copy sh1.Rows(rw) r.Offset(10, 0).Select Application.CutCopyMode = False End Sub ANSWER: Ben, -- ---- If you paste to AW1, you can't paste the entire row as you inferred in your original post. So I guess there are some numbers of columns you actually want to copy and paste. I will guess 20 and I have revised the code to do that If I read you code and explanation, you are deleting rows in the activesheet of workbooks Workbooks("data measurement_given_date_time.lvm") and at the end of those deletions you select cell AW1 which is where you want the data copied to - the data being the row and 9 rows under selected in the workbook Windows("2009-Sep Weather Data.csv") given that then: Sub xx00_macro() Dim bk1 As Workbook Dim sh As Worksheet, rw As Long, r As Range Dim bk As Workbook ' ' xx00_macro Macro ' Macro recorded 9/29/2010 by Ben Schams ' ' Keyboard Shortcut: Option+Cmd+b ' Rows("2:600").Select Selection.Delete Shift:=xlUp Rows("3:601").Select Selection.Delete Shift:=xlUp Rows("4:602").Select Selection.Delete Shift:=xlUp Rows("5:603").Select Selection.Delete Shift:=xlUp Rows("6:604").Select Selection.Delete Shift:=xlUp Rows("7:605").Select Selection.Delete Shift:=xlUp Rows("8:606").Select Selection.Delete Shift:=xlUp Rows("9:607").Select Selection.Delete Shift:=xlUp Rows("10:608").Select Selection.Delete Shift:=xlUp Rows("11:609").Select Selection.Delete Shift:=xlUp Range("AW1").Select Set bk1 = ActiveWorkbook Set r2 = Range("AW1") Set bk = Workbooks("2009-Sep Weather Data.csv") bk.Activate Set sh = ActiveSheet Set r = sh.Cells(ActiveCell.Row, 1) r.Resize(10, 1).resize(,20).Copy r2 ' End Sub ---------- FOLLOW-UP ---------- QUESTION: I havent been able to run this macro. I cannot seem to avoid the error "Sub or function not defined" I have two spreadsheets open, Lets call them A and B. I want to copy and paste 10 rows from A into B. The problem that I am having with my coding is that I don't want to copy and paste the same rows from A into B everytime. Rather, I want to copy10 rows down from the currently selected row in A and then paste them in B. And after they have been pasted in B, return to A and select the 11th row so that the next time the macro is run it goes in a copy/paste sequence of 10 rows at a time. here is what the coding currently looks like for this operation: Rows("1561:1570").Select Selection.Copy Windows("data measurement_09-09-08_0630.lvm").Activate Application.CutCopyMode = False ActiveSheet.Paste

• Answer:

  Ben, I copied the code and pasted it into a new workbook I selected 2009-Sep Weather Data.csv and selected a cell in that sheet to mark where I wanted to start copying from I then activated data measurement_09-09-08_0630.lvm I ran the macro and it copied the 10 rows and 20 columns of data (starting with column A) of 2009-Sep Weather Data.csv (active worksheet in that workbook) to cell AW1 of the activesheet of workbook data measurement_09-09-08_0630.lvm. I reactivated workbook data measurement_09-09-08_0630.lvm and ran the code and it copied the next 10 rows and 20 columns from 2009-Sep Weather Data.csv to cell AW1. So it appears to be working quite well and as designed in my tests. I don't know where you are getting that error from. Perhaps you had a problem in copying the code. (you didn't post the code you are actually trying to run, so I can only guess - and it you did post it, you should specify what line is highlighted when you get the error). Ben The following code will copy 10 rows down from the active cell on sheet1 to sheet2 and return to sheet1 in the eleventh row. It will always copy to the same place on sheet 2; I don't know if that is what you want, but you should be able to adjust the code as necessary if you don't always want to go to the same place in the second sheet. Sub test() Selection.Resize(10, 1).EntireRow.Copy Sheet2.Activate ActiveSheet.Cells(1, 1).Activate ActiveSheet.Paste Sheet1.Activate Selection.Cells(1, 1).Offset(10, 0).Activate Application.CutCopyMode = False End Sub