[VBA] Copying cells from one sheet to another

[rhysm144]

I am trying to write a macro to copy cells in the following pattern:
B1 from Sheet 1 (Actually called Names) into cell AC2 in Sheet 2 followed by;
B2 from Sheet 1 into cell AC2 in Sheet 3 followed by;
B3 from Sheet 1 into cell AC2 in Sheet 3 followed by etc until the end of values in Column B

For the record all sheets contain unique names and do not follow the pattern of Sheet1,Sheet2 etc

Any help would be appreciated.

[Kenneth Hobs]

I don't see a pattern as you overwrote Sheet3.[AC2] in the last two so the last copy would govern.

[rhysm144]

Apologies, typo. What I meant was;

B1 from Sheet 1 (Actually called Names) into cell AC2 in Sheet 2 followed by;
B2 from Sheet 1 into cell AC2 in Sheet 3 followed by;
B3 from Sheet 1 into cell AC2 in Sheet 4 followed by;
B4 from Sheet 1 into cell AC2 in Sheet 5 followed by;
B5 from Sheet 1 into cell AC2 in Sheet 6 followed by; etc until the end of values in Column B

[Kenneth Hobs]

You can attach a short example obfuscated file if you like. In a reply, select the Go Advanced button in lower right, and then the paperclip icon in the toolbar or Manage Attachment link after reply block and then Browse and Upload.

I may not need that though. You said that the sheet names are not in pattern/sequence. You can reference sheets in 3 basic ways: (1) Worksheets(1), (2) Worksheets("Sheet Name Here"), and Sheet1. These are by index number, sheet name, and sheet codename respectively. (1) and (2) are easily done in a loop if there is a sequential pattern.

So, say which of the 3 methods you mean or post an example file.

[rhysm144]

I believe I mean Method 1 as it would be too arduous to manually change the unique sheet names for each individual worksheet as required by method 2.

Apologies if I am incorrect, as I am sure it is evident my vba skills are limited

[Kenneth Hobs]

Sub Main()  
  Dim i As Long
  On Error Resume Next
  With Worksheets(1)
    For i = 1 To .Cells(Rows.Count, "B").End(xlUp).Row
      .Cells(i, "B").Copy Worksheets(i + 1).Range("AC2")
    Next i
  End With
End Sub

[rhysm144]

Worked Perfectly.

Thank you