I am facing some difficulty in achieving the desired result for my macro
.
Intention:
I have a list of data in sheets(input).column A
(the number of rows that has value will vary and hence I created a loop that will run the macro until the activecell is blank).
My macro starts from Range(A2)
and stretches all the way down column A, it stops only when it hits a blank row
Desired result for the macro will be to start copying the cell value in sheet(input).Range(A2)
paste it to sheet(mywork).Range(B2:B6)
.
For example, if "Peter" was the value in cell sheet(input),range(A2)
then when the marco runs and paste the value into sheet(mywork) range(B2:B6)
. ie range B2:B6
will reflect "Peter"
Then the macros loop back to sheet(input) & copy the next cell value and paste it to range(B7:B10)
Example: "Dave" was the value in sheet(input) Range(A3)
, then "Dave" will be paste into the next 4 rows in sheet(mywork).Range(B7:B10)
. B7:B10
will reflect "Dave"
Again repeating the same process goes back to sheet(input) this time range(A4)
, copys the value goes to sheet(mywork) and paste it into B11:B15
.
Basically the process repeats....
The macro ends the when the activecell in sheet(input) column A
is empty.
Sub playmacro()
Dim xxx As Long, yyy As Long
ThisWorkbook.Sheets("Input").Range("A2").Activate
Do While ActiveCell.Value <> ""
DoEvents
ActiveCell.Copy
For xxx = 2 To 350 Step 4
yyy = xxx + 3
Worksheets("mywork").Activate
With ActiveSheet
.Range(Cells(xxx, 2), Cells(yyy, 2)).PasteSpecial xlPasteValues
End With
Next xxx
ThisWorkbook.Sheets("Input").Select
ActiveCell.Offset(1, 0).Activate
Loop
Application.ScreenUpdating = True
End Sub
One way to loop through a range is to use the For... Next loop with the Cells property. Using the Cells property, you can substitute the loop counter (or other variables or expressions) for the cell index numbers. In the following example, the variable counter is substituted for the row index.
In the Mapper, select the fields that you want to include in the loop, starting with the row for the first screen that contains the fields, and ending with the row for the last field. Click Create Loop. If Create Loop is not available, click the Expert View tab.
Private Sub CommandButton1_Click()
Dim Z As Long
Dim Cellidx As Range
Dim NextRow As Long
Dim Rng As Range
Dim SrcWks As Worksheet
Dim DataWks As Worksheet
Z = 1
Set SrcWks = Worksheets("Sheet1")
Set DataWks = Worksheets("Sheet2")
Set Rng = EntryWks.Range("B6:ad6")
NextRow = DataWks.UsedRange.Rows.Count
NextRow = IIf(NextRow = 1, 1, NextRow + 1)
For Each RA In Rng.Areas
For Each Cellidx In RA
Z = Z + 1
DataWks.Cells(NextRow, Z) = Cellidx
Next Cellidx
Next RA
End Sub
Alternatively
Worksheets("Sheet2").Range("P2").Value = Worksheets("Sheet1").Range("L10")
This is a CopynPaste - Method
Sub CopyDataToPlan()
Dim LDate As String
Dim LColumn As Integer
Dim LFound As Boolean
On Error GoTo Err_Execute
'Retrieve date value to search for
LDate = Sheets("Rolling Plan").Range("B4").Value
Sheets("Plan").Select
'Start at column B
LColumn = 2
LFound = False
While LFound = False
'Encountered blank cell in row 2, terminate search
If Len(Cells(2, LColumn)) = 0 Then
MsgBox "No matching date was found."
Exit Sub
'Found match in row 2
ElseIf Cells(2, LColumn) = LDate Then
'Select values to copy from "Rolling Plan" sheet
Sheets("Rolling Plan").Select
Range("B5:H6").Select
Selection.Copy
'Paste onto "Plan" sheet
Sheets("Plan").Select
Cells(3, LColumn).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
LFound = True
MsgBox "The data has been successfully copied."
'Continue searching
Else
LColumn = LColumn + 1
End If
Wend
Exit Sub
Err_Execute:
MsgBox "An error occurred."
End Sub
And there might be some methods doing that in Excel.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With