Copy (3) specific values from 375 worksheets, paste into another worksheet

[ajdinspector]

Hello, HELP!
I am trying to write a vba script to copy three values from 375 worksheets and paste into another worksheet. All the 375 worksheets are in the same format, on each worksheet i need
cell "G3" copied to Sheet12 cell "A4" ,
cell "C50" copied to Sheet12 cell "B4",
cell "G50" copied to Sheet12 cell "D4",
cell "I50" copied to Sheet12 cell "F4"

then move to next sheet and
cell "G3" copied to Sheet12 cell "A5" ,
cell "C50" copied to Sheet12 cell "B5",
cell "G50" copied to Sheet12 cell "D5",
cell "I50" copied to Sheet12 cell "F5"

This is what i have, but it's not working

Sub CopyDFs()
Dim wb As Workbook
Dim ws As Range
Dim co As Range
Dim df1 As Range
Dim df2 As Range
Dim df3 As Range
Dim copyco As Range
Dim copydf1 As Range
Dim copydf2 As Range
Dim copydf3 As Range
Set wb = ThisWorkbook
Set ws = wb.Sheets(13): Sheets.Count
Set co = Worksheets(12).Range("A" & Rows.Count).End(xlUp).Offset(4, 0)
Set df1 = Worksheets(12).Range("B" & Rows.Count).End(xlUp).Offset(4, 0)
Set df2 = Worksheets(12).Range("D" & Rows.Count).End(xlUp).Offset(4, 0)
Set df3 = Worksheets(12).Range("F" & Rows.Count).End(xlUp).Offset(4, 0)
Set copyco = Range("G3")
Set copydf1 = Range("C50")
Set copydf2 = Range("G50")
Set copydf3 = Range("I50")
For Each ws In ActiveWorkbook.Worksheets
copyco.Copy
co.PasteSpecial xlPasteValues
copydf1.Copy
copydf1.PasteSpecial xlPasteValues
copydf2.Copy
copydf2.PasteSpecial xlPasteValues
copydf3.Copy
copydf3.PasteSpecial xlPasteValues
Next ws
End Sub

Thanks so much for the help

 

[Fluff]

Hi & welcome to MrExcel.
Do you want the data from the thirteenth sheet to the last sheet copied to the twelfth sheet?

 

[ajdinspector]

Hi! Thanks! Yes, from the thirteenth to the end of the workbook.

 

[Fluff]

Ok how about

Sub CopyData()
   Dim Mws As Worksheet
   Dim i As Long
   
   Set Mws = Sheets(12)
   For i = 13 To Worksheets.Count
      With Sheets(i)
         Mws.Range("A" & i - 9).Value = .Range("G3").Value
         Mws.Range("B" & i - 9).Value = .Range("C50").Value
         Mws.Range("D" & i - 9).Value = .Range("G50").Value
         Mws.Range("F" & i - 9).Value = .Range("I50").Value
      End With
   Next i
End Sub

 

[ajdinspector]

WOW! Yes, that worked great!! Thanks so much!! I guess i really over-complicated that one!

 

[Fluff]

Glad to help & thanks for the feedback.
One thing to be aware of, is that if somebody changes the order of the sheets, it could muck-up your workbook.

 

[ajdinspector]

Ah, ok. Is there a way to reference the sheets code name so that can't happen? I'll have to use this is the future with a different data set.

 

[Fluff]

You can do it like

Sub CopyData()
   Dim Mws As Worksheet
   Dim Ary As Variant, Sht As Variant
   Dim i As Long
   
   Ary = Array(Sheet12, Sheet6, Sheet7)
   Set Mws = Sheet13
   i = 3
   For Each Sht In Ary
      i = i + 1
      With Sht
         Mws.Range("A" & i).Value = .Range("G3").Value
         Mws.Range("B" & i).Value = .Range("C50").Value
         Mws.Range("D" & i).Value = .Range("G50").Value
         Mws.Range("F" & i).Value = .Range("I50").Value
      End With
   Next Sht
End Sub

 

[ajdinspector]

Thanks, I'll try this after i meet my deadline!