Macro copy and paste using loop

Question

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?

What have you done so far? Check this to help you start [link](http://stackoverflow.com/questions/37107574/copy-and-paste-specific-cells-from-one-worksheet-to-another) — Rémi, Jul 07 '16 at 15:03
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`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) to tighten it up, and learn how to work directly with the data. — BruceWayne, Jul 07 '16 at 15:04
Dont use copy and paste in Excel unless you need to copy formats. Just use cells/ranges and their values and set them. — Mono, Jul 07 '16 at 15:04

Tim Edwards · Answer 1 · 2016-07-07T15:30:35.197

1

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

edited Jul 07 '16 at 15:30

answered Jul 07 '16 at 15:05

Tim Edwards

1,031
1
13
34

Don't use the copy and paste and select and activate functions unless it is absolutely necessary. Copy and paste will erase your clipboard and it usually leads to "flicker" macros. Use .Values of ranges and assign them to other ranges. – Mono Jul 07 '16 at 15:24
I think he was after help with looping but I get your point and will edit accordingly, not sure it necessarily warranted a down-vote though as it answered the question... – Tim Edwards Jul 07 '16 at 15:29
Answering the question is one thing, but a good answer is better then an answer which leads to bad habits imho. – Mono Jul 07 '16 at 15:38
thank you so much. I tried it and it worked. The only thing is that I do not want to include data form Sheet2. Is there an easy way to edit the code, or would it be easier to delete sheet2? – simen Jul 07 '16 at 15:42
You could just change `For i= 2 to j` to `For i = 3 to j` – Tim Edwards Jul 07 '16 at 15:46

score 1 · Answer 2 · answered Jul 07 '16 at 15:18

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

Macro copy and paste using loop

2 Answers2