Macro copy and paste using loop

0
votes

I am trying to make a summary by copying info from different worksheets into a summary worksheet. More specifically, I am trying to copy cell O3 and O4 from sheet3 into cell D3 and E3 in sheet1, respectively. Then I want to move on and copy cell O3 and O4 from sheet 4 into cell D4 and E4 in sheet1, respectively and so on through all my sheets.

I am completely new to macro coding so would really appreciate any help, thanks. I am assuming one would have to use a loop.

P.S the worksheets are named after companies (for example "DataGroup" not "Sheet3") will that affect the code?

2
excelvbaloopsmacroscopy-paste
What have you done so far? Check this to help you start link – Rémi
Turn on the macro recorder, then go through the steps you outline above. This will give you some code to start with, then read through how to avoid .Select to tighten it up, and learn how to work directly with the data. – BruceWayne
Dont use copy and paste in Excel unless you need to copy formats. Just use cells/ranges and their values and set them. – Mono

2 Answers

1
votes

If you have a workbook containing some worksheets and one of them is called Summary, then this code will copy the value of cell A1 from each of the other worksheets into column A of Summary. You can adapt this to suit your needs.

Dim ws As Worksheet, rowDest As Integer
rowDest = 1
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Summary" Then
        Worksheets("Summary").Cells(rowDest, 1).Value = ws.Range("A1").Value
        rowDest = rowDest + 1
    End If
Next ws
1
votes

If you're looking to loop through your sheets you don't need to reference them explicitly i.e. Sheets("DataGroup") you could use Sheets(1), hence making it easy to loop through with each integer.

e.g.

Sub Copying()
Dim i As Integer, j As Integer
j = Sheets.Count
For i = 2 To j
    Sheets(1).Range("D" & i + 1) = Sheets(i).Range("O3").Value
    Sheets(1).Range("E" & i + 1) = Sheets(i).Range("O4").Value
Next i
End Sub