Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
constructing a copy-paste loop that skips rows
Hello. Yet another newbie question here.
I am trying to write a macro, starting with A4, copies the text in every 44th row and pastes the text into another worksheet. It should do this for a user defined number of times. The first cell of interest is A4, the next would then be A48, etc... I can't figure out how to construct the loop. I have tried it using Step (is this right?) and also using variables and Offset. I have gotten so confused that I don't know what I am doing at this point! Here is the mess that I have at the moment: Sub test2() Dim StartValue As Integer Dim n As Integer n = CInt(InputBox("Enter the number of inputs: ")) StartValue = 4 For Count = 1 To n Step 44 ActiveCell.Offset(StartValue, 0).Select Selection.Copy Sheets("Sheet2").Activate ActiveCell(Count, 0).Select ActiveSheet.Paste Sheets("Sheet1").Activate Next Count End Sub Any help would be much appreciated! Thanks, H.M. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
constructing a copy-paste loop that skips rows
Hi,
with the code as stated, the loop will terminate when count becomes larger than n, and count will increase with 44 (the step) for each iteration of the loop. Since you want to iterate the loop n times, it might be easier to simply loop with step one and calculate the row index at the start of each iteration. E.g., For i=1 to n rowIndex = 44*n ... Next i Remember KISS: Keep It Simple Stupid! (In hardware design also means Keep It Strictly Synchronous.) That at least helps me every day! Dag :) -- DagOystein.Johansen AT sage.com -----Original Message----- Hello. Yet another newbie question here. I am trying to write a macro, starting with A4, copies the text in every 44th row and pastes the text into another worksheet. It should do this for a user defined number of times. The first cell of interest is A4, the next would then be A48, etc... I can't figure out how to construct the loop. I have tried it using Step (is this right?) and also using variables and Offset. I have gotten so confused that I don't know what I am doing at this point! Here is the mess that I have at the moment: Sub test2() Dim StartValue As Integer Dim n As Integer n = CInt(InputBox("Enter the number of inputs: ")) StartValue = 4 For Count = 1 To n Step 44 ActiveCell.Offset(StartValue, 0).Select Selection.Copy Sheets("Sheet2").Activate ActiveCell(Count, 0).Select ActiveSheet.Paste Sheets("Sheet1").Activate Next Count End Sub Any help would be much appreciated! Thanks, H.M. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Loop to Find then Copy & Paste | Excel Discussion (Misc queries) | |||
Loop thru rows to copy to another excel spreadsheet | Excel Worksheet Functions | |||
Data Validation skips rows | Setting up and Configuration of Excel | |||
Hide Rows - copy and paste only rows that show | Excel Worksheet Functions | |||
copy,paste and loop through workbook | Excel Discussion (Misc queries) |