For Loop Offset

Y

youngst7

New Member
Joined
Sep 17, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hi there, I'm new to Macros and am trying to set up a for loop between two worksheets. I have been able to create a macro that reads the first row of data but I want the macro to continue running down the next 149 rows of data using offset but can't figure it out. Any help is appreciated! I have included the code of the macro for the first row. I recorded copying and pasting between the worksheets to create the macro but if there is a better way to run it that would be helpful also!

VBA Code: 
Sub Macro1()

    Range("B2").Select
    Selection.Copy
    Sheets("Analysis").Select
    Range("C9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Data").Select
    Range("D2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Analysis").Select
    Range("C10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Data").Select
    Range("E2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Analysis").Select
    Range("C11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Data").Select
    Range("F2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Analysis").Select
    Range("C12").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B41").Select
    Sheets("Data").Select
    Range("H2").Select
    Sheets("Analysis").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Data").Select
    Range("J2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Analysis").Select
    Range("C29").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("O2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Data").Select
    Range("K2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Analysis").Select
    Range("O7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Data").Select
    Range("L2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Analysis").Select
    Range("O8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Data").Select
    Range("M2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Analysis").Select
    Range("O10").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Data").Select
    Range("N2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Analysis").Select
    Range("O12").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Data").Select
    Range("O2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Analysis").Select
    Range("O13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Data").Select
    Range("P2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Analysis").Select
    Range("O15").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Data").Select
    Range("Q2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
Hi and welcome to MrExcel.

It is simpler than it seems.
But I don't understand some of your data.
I show you another approach for the data in row 2.
You show me in which cells you want the data for row 3 and I help you complete the cycle.

VBA Code: 
Sub macro2()
  'Pass data from the "Data" sheet to the "Analysis" sheet
  Sheets("Analysis").Range("C9").Value = Sheets("Data").[B2]
  Sheets("Analysis").Range("C10").Value = Sheets("Data").[D2]
  Sheets("Analysis").Range("C11").Value = Sheets("Data").[E2]
  Sheets("Analysis").Range("C12").Value = Sheets("Data").[F2]
  Sheets("Analysis").Range("C29").Value = Sheets("Data").[J2]
  Sheets("Analysis").Range("B41").Value = Sheets("Data").[F2]  '<--- Could you confirm if this is correct
  
  'Pass data from the "Analysis" sheet to the "Data" sheet
  Sheets("Data").Range("K2").Value = Sheets("Analysis").[O2]
  Sheets("Data").Range("L2").Value = Sheets("Analysis").[O7]
  Sheets("Data").Range("M2").Value = Sheets("Analysis").[O8]
  Sheets("Data").Range("N2").Value = Sheets("Analysis").[O10]
  Sheets("Data").Range("O2").Value = Sheets("Analysis").[O12]
  Sheets("Data").Range("P2").Value = Sheets("Analysis").[O13]
  Sheets("Data").Range("Q2").Value = Sheets("Analysis").[O15]
  
  'Then, based on the above, put the movements for the next row.
  'That is, I need to know which cells go from the data sheet to the analyis sheet.
  'and which cells go from the analysis sheet to the data sheet.
  'With those 2 examples, I could identify the results and I can cycle.
  
End Sub
 
Upvote 0
DanteAmor said:
Hi and welcome to MrExcel.

It is simpler than it seems.
But I don't understand some of your data.
I show you another approach for the data in row 2.
You show me in which cells you want the data for row 3 and I help you complete the cycle.

VBA Code: 
Sub macro2()
  'Pass data from the "Data" sheet to the "Analysis" sheet
  Sheets("Analysis").Range("C9").Value = Sheets("Data").[B2]
  Sheets("Analysis").Range("C10").Value = Sheets("Data").[D2]
  Sheets("Analysis").Range("C11").Value = Sheets("Data").[E2]
  Sheets("Analysis").Range("C12").Value = Sheets("Data").[F2]
  Sheets("Analysis").Range("C29").Value = Sheets("Data").[J2]
  Sheets("Analysis").Range("B41").Value = Sheets("Data").[F2]  '<--- Could you confirm if this is correct
 
  'Pass data from the "Analysis" sheet to the "Data" sheet
  Sheets("Data").Range("K2").Value = Sheets("Analysis").[O2]
  Sheets("Data").Range("L2").Value = Sheets("Analysis").[O7]
  Sheets("Data").Range("M2").Value = Sheets("Analysis").[O8]
  Sheets("Data").Range("N2").Value = Sheets("Analysis").[O10]
  Sheets("Data").Range("O2").Value = Sheets("Analysis").[O12]
  Sheets("Data").Range("P2").Value = Sheets("Analysis").[O13]
  Sheets("Data").Range("Q2").Value = Sheets("Analysis").[O15]
 
  'Then, based on the above, put the movements for the next row.
  'That is, I need to know which cells go from the data sheet to the analyis sheet.
  'and which cells go from the analysis sheet to the data sheet.
  'With those 2 examples, I could identify the results and I can cycle.
 
End Sub
Click to expand...
So, need to copy numbers from cells B2, D2, E2, F2, H2, and J2 of the Data sheet over to C9, C10, C11, C12, C19 and C29 on the Analysis sheet respectively. There are formulas in the Analysis sheet that I want to then bring back the numbers in Analysis O2, O7, O8, O10, O13, and O9 and paste them in Data K2, L2, M2, N2, O2, P2 and Q2 respectively. Then I want it to run the same function for the rest of the sheet. I was able to record a macro which I posted initially which runs on the first line but I don't know how to get it to run successfully the rest of the 149 rows down the Data sheet.
 
Upvote 0
Try this:

VBA Code: 
Sub macro2()
  Dim shD As Worksheet, shA As Worksheet
  Dim i As Long
  
  Set shD = Sheets("Data")
  Set shA = Sheets("Analysis")
  
  For i = 2 To shD.Range("B" & Rows.Count).End(3).Row
  
    'copy numbers from cells B2, D2, E2, F2, H2, and J2 of the Data sheet
    'over to C9, C10, C11, C12, C19 and C29 on the Analysis sheet respectively.
    shA.Range("C9").Value = shD.Range("B" & i).Value
    shA.Range("C10").Value = shD.Range("D" & i).Value
    shA.Range("C11").Value = shD.Range("E" & i).Value
    shA.Range("C12").Value = shD.Range("F" & i).Value
    shA.Range("C19").Value = shD.Range("H" & i).Value
    shA.Range("C29").Value = shD.Range("J" & i).Value
    
    'in Analysis O2, O7, O8, O10, O13, and O9 and paste them in Data K2, L2, M2, N2, O2, P2 and Q2
    'According to your code:
    'in Analysis O2, O7, O8, O10, O12, O13 and O15 and paste them
    'in Data K2, L2, M2, N2, O2, P2 and Q2 respectively.
    shD.Range("K" & i).Value = shA.Range("O2").Value
    shD.Range("L" & i).Value = shA.Range("O7").Value
    shD.Range("M" & i).Value = shA.Range("O8").Value
    shD.Range("N" & i).Value = shA.Range("O10").Value
    shD.Range("O" & i).Value = shA.Range("O12").Value
    shD.Range("P" & i).Value = shA.Range("O13").Value
    shD.Range("Q" & i).Value = shA.Range("O15").Value
  Next
End Sub
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Counter in For Each loop
Replies
2
Views
196
Possum17
P
A
  • Question
Macro to update list to last row on another sheet copy source from multiple sheets
Replies
0
Views
308
Azamamir
A
P
  • Solved
Need help with macro Dynamic Range??
Replies
6
Views
172
pennbowl
P
J
  • Question
VBA to select partial filename and go back and forth between two files to copy paste
Replies
1
Views
166
shknbk2
S
D
  • Question
VBA optimization advice
Replies
3
Views
95
bobsan42
bobsan42

Forum statistics

Threads
1,214,915
Messages
6,122,214
Members
449,074
Latest member
cancansova

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top