copy the values from one sheet and pasting it to other skipping rows

[Suchi D]

Hi,

I am new to VBA. I am trying to do one thing regarding copy and paste. Here is my question.


I want to count the number of values in the column A of sheet1 and want to paste them in the column D of Sheet 2 skipping 7 rows.
pattern goes like:
sheet1.A2 copy and pasted to sheet2.D2
sheet1.A3 copy and pasted to Sheet2.D9
sheet1.A4 copy and pasted to Sheet2.D16 and so..with the difference of 7.

Can anyone please help me with it?

 

[Mike LH]

Hi,

Try this code.

Sub somesub()
Dim LastRow As Long, C As Range
Dim Myrange As Range, x As Long
Dim Src As Worksheet, Dst As Worksheet
Set Src = Sheets("Sheet1")
Set Dst = Sheets("Sheet2")
LastRow = Src.Cells(Rows.Count, "A").End(xlUp).Row
Set Myrange = Src.Range("A2:A" & LastRow)
x = 2
For Each C In Myrange
C.Copy Dst.Range("D" & x)
x = x + 7
Next
End Sub

 

[Suchi D]

Thank you so much. It works absolutely fine.

 

[Suchi D]

Hi, There is one more issue. This code is copying the range. But if I want to copy the range which consists of formulas, but I only want the values to be copied. then how can I make the changes in this code, if in sheet1 A2 to A. lastrow is a range of formulas?

 

[JackDanIce]

Try:

Sub Macro1()

Dim LR      As Long
Dim x       As Long: x = 2
Dim rngItr  As Excel.Range
Dim rngSrc  As Excel.Range

Application.ScreenUpdating = False

With Sheets("Sheet1")
    LR = .Cells(.Rows.Count, 1).End(xlUp).Row
    Set rngSrc = .Range("A2").Resize(LR-1)
End With

With Sheets("Sheet2")
    For Each rngItr In rngSrc
        .Range("D" & x).Value = rngItr.Value
        x = x + 7
    Next rngItr
End With

Set rngItr = Nothing
Set rngSrc = Nothing

Application.ScreenUpdating = True

End Sub

 

[Mike LH]

Hi, There is one more issue. This code is copying the range. But if I want to copy the range which consists of formulas, but I only want the values to be copied. then how can I make the changes in this code, if in sheet1 A2 to A. lastrow is a range of formulas?

Hi,

Like this

Sub somesub()
Dim LastRow As Long, C As Range
Dim Myrange As Range, x As Long
Dim Src As Worksheet, Dst As Worksheet
Set Src = Sheets("Sheet1")
Set Dst = Sheets("Sheet2")
LastRow = Src.Cells(Rows.Count, "A").End(xlUp).Row
Set Myrange = Src.Range("A2:A" & LastRow)
x = 2
For Each C In Myrange
Dst.Range("D" & x).Value = C.Value
x = x + 7
Next
End Sub

 

[Suchi D]

You are a genious :D thnk u sooooooooooooo much

 

[Suchi D]

Also, If I just want a shortcut to run these macro then what can be done? for now, I am going to View Macro and then executing the macros.