VBA Loop Data Copy Across Worksheets

[ibrkad]

Hello everyone,

I am quite new to VBA and trying to learn it by creating the complex scenarios in the random excel calculations. I have a question regarding using VBA to copy the data across worksheets. I would appreciate if you could kindly help me with it.

I am trying to copy multiple data from one worksheet to another. In my case,

1- Copy the value from D2 of Sheet1 to H26 of Sheet2
2- Copy the value from H2 of Sheet1
.......2-1- if A1 of Sheet2 is empty, to H27 of Sheet2
.......2-2- else to H28 of Sheet2
3- Copy the value from I2 of Sheet1
.......3-1- if A1 of Sheet2 is empty, to H28 of Sheet2
.......3-2- else to H27 of Sheet2
4- Copy the result from H35 of Sheet2 to J2 of Sheet1
5- Copy the result from H28 of Sheet3 to K2 of Sheet1
6- Continue the process until the end of last row in Sheet 1

I have some calcs in sheet 2 and 3 which are linked to each other so input in sheet 2, which should be coming from sheet 1 also gives some results in sheet 3 and I am trying to take the outputs from sheet 2 and 3, and compile them in sheet 1.

Everything got complicated especially with if clause and multiple data copy. I can create the logic and flow in my mind but I could not manage to run it. Any help from your side would be very much appreciated.

Thanks a lot for your help in advance!

 

[DanteAmor]

Hi and welcome to MrExcel

Try this:

Sub vbaloop()
  Dim sh1 As Worksheet
  Dim c As Range
  
  Set sh1 = Sheets("Sheet1")
  For Each c In sh1.Range("D2", sh1.Range("D" & Rows.Count).End(3))
    With Sheets("Sheet2")
      .Range("H26").Value = c.Value
      If .Range("A1").Value = "" Then
        .Range("H27").Value = sh1.Range("H" & c.Row)
        .Range("H28").Value = sh1.Range("I" & c.Row)
      Else
        .Range("H28").Value = sh1.Range("H" & c.Row)
        .Range("H27").Value = sh1.Range("I" & c.Row)
      End If
      sh1.Range("J" & c.Row).Value = .Range("H35").Value
      sh1.Range("K" & c.Row).Value = Sheets("Sheet3").Range("H28").Value
    End With
  Next
End Sub

 

[ibrkad]

Hi,

Thanks a lot for this but I think I am a bit struggling to get the results that I wanted, if I am not missing something. It is probably my bad that I should have included more explanation.

1- Copy the value from D2 of Sheet1 to H26 of Sheet2
(during the loop, the data will come from D3, D4, D5 and so on but will be always and input to H26)

2- Copy the value from H2 of Sheet1
.......2-1- if A1 of Sheet2 is empty, to H27 of Sheet2
.......2-2- else to H28 of Sheet2
(during the loop, the data will come from H3, H4, H5 and so on but will be always an input to H27 or H28, always depending on A1)

3- Copy the value from I2 of Sheet1
.......3-1- if A1 of Sheet2 is empty, to H28 of Sheet2
.......3-2- else to H27 of Sheet2
(during the loop, the data will come from I3, I4, I5 and so on but will be always an input to H28 or H27, always depending on A1)

4- Copy the result from H35 of Sheet2 to J2 of Sheet1
(during the loop, the result will come from always H35 but will be an input to H35, H36, H37 and so on)

5- Copy the result from H28 of Sheet3 to K2 of Sheet1
(during the loop, the result will come from always H28 but will be an input to K2, K3, K4 and so on)

6- Continue the process until the end of last row in Sheet 1
(it is basically to indicate that it is a loop)

Hope it is not getting more complicated and time consuming for you now...

Thanks a lot for your help again.

 

[ibrkad]

Actually that works! I somehow made mistake when copying the VBA over...

Thanks a lot for this! You are the superstar!!!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.