title: Creating a macro that copies certain cells and places them into columns
body: <p>I have 26 worksheets every week that contain a "Station #", "Latitude#", and a "Longitude#". 
  I want to create a Macro that grabs these 3 cells, copies them and places them into 3 columns named "Station #", "Lat", and "Long. 
  I'm not very good at this so i need some help. 
  This is what i got so far:</p>
  
  <pre><code>Sub Macro1()
  
  FolderName = "C:\Users\Captain Wypij\Desktop\Traffic\test"
  If Right(FolderName, 1) &lt;&gt; Application.PathSeparator Then FolderName = FolderName &amp; Application.PathSeparator
  Fname = Dir(FolderName &amp; "*.xls")
  
      'loop through the files
  Do While Len(Fname)
  
      With Workbooks.Open(FolderName &amp; Fname)
  
  Range("C8:D8").Select
  Selection.Copy
  ChDir "C:\Users\Captain Wypij\Desktop\Traffic"
  Workbooks.Open Filename:= _
      "C:\Users\Captain Wypij\Desktop\Traffic\Test.xls.xlsx"
  Range("A2").Select
  If ("A2") = "*" Then Range("A3").Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
  Workbooks.Open (FolderName &amp; Fname)
  ActiveWindow.SmallScroll Down:=12
  Range("C34:D34").Select
  Application.CutCopyMode = False
  Selection.Copy
  Windows("Test.xls.xlsx").Activate
  Range("B2").Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
  Workbooks.Open (FolderName &amp; Fname)
  Range("G34:H34").Select
  Application.CutCopyMode = False
  Selection.Copy
  Windows("Test.xls.xlsx").Activate
  Range("C2").Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
  Workbooks.Open (FolderName &amp; Fname)
   ActiveWindow.Close
  
   Windows("Test.xls.xlsx").Activate
  ActiveWorkbook.Save
  ActiveWindow.Close
  
  End With
      ' go to the next file in the folder
      Fname = Dir
  Loop
  End Sub
  </code></pre>
  
  <p>I cant seem to figure out how to paste the next worksheet i open in the next fields ( such as A3, B3, C3 and so forth. 
  Please help me! </p>