PDA

View Full Version : MACRO Help



oldboy
07-08-2015, 11:10 AM
Hey Guys,

I have a code that I got which is essentially calling files from a specific Extention (Column AD).
And within that file, goto a tab named "FS" and copy and paste values/format to the adjacent tab name in Column A. So if it is from File.xlsx, it will take the tab named "FS" within "E:\File.xlsx" and copy and paste it into the
tab
named "Tab Name 1" in Column A.


Column A
Column AD


Tab Names Below
File Director Below


Tab 1
E:\File.xlsb


Tab 2
E:\File 2.xlsb


Tab 3
E:\File 3.xlsb


Tab 4
E:\File 4.xlsb




Please see below for code

Sub copyStuff()
Dim wb As Workbook, sh As Worksheet, c As Range

For Each c In ActiveSheet.Range("A5", ActiveSheet.Cells(Rows.Count, 2).End(xlUp))
On Error Resume Next
Set wb = Workbooks.Open(c.Value)
Set sh = Sheets("FS")
sh.UsedRange.Copy ThisWorkbook.Sheets(c.Offset(0, -1).Value).Range("A")
wb.Close False
On Error GoTo 0
Next
End Sub



Basically this code seems to call the file and goto the tab named "FS" but would not copy to the tab name on Column A.

If someone can help me fix this or explain what this code is doing exactly, I would greatly appreciate it!\


Thanks

SamT
07-08-2015, 12:37 PM
Sub copyStuff()
'For help see: http://www.vbaexpress.com/forum/showthread.php?53124-MACRO-Help
Dim wb As Workbook, sh As Worksheet, c As Range

For Each c In ActiveSheet.Range("A5", ActiveSheet.Cells(Rows.Count, 2).End(xlUp))
On Error Resume Next
Set wb = Workbooks.Open(c.Value)
Set sh = wb.Sheets("FS") '<<<<<<<<<<<<<
sh.UsedRange.Copy ThisWorkbook.Sheets(c.Offset(0, -1).Value).Range("A1") '<<<<<<<<<<<
wb.Close False
On Error GoTo 0
Next
End Sub