VBA to replace dynamic sheet/cell reference

[GetSmart]

Hi All,

I need a VBA solution which will perform a sheet to sheet range copy function using dynamic sheet name references. The current template that I'm working with uses the indirect and concatenate functions to perform the same thing, but since this template will be expanded to import dozens of .txt files (each named by number, creating an individual sheet for each file), the formula references will make the file too big to use since it will potentially have a table of 100 columns and 15,000 rows.

The script I already have sets up the following scenario:
Each imported .txt file creates a numbered sheet containing 2 columns of data (A and B), starting at row 25 and ending at row 15025. On each imported sheet, column A contains a timestamp value, and column B contains a sampling value. If 3 files were imported with the file names 11.txt, 12.txt, 13.txt, the workbook will have this data in sheets named "11", "12" and "13". Sometimes 30 files will be imported and sometimes 50 or 60. In each case, there will be as many numbered sheets as there were files imported using this script and the sheet names will match the .txt file names.

The main template sheet needs to do the following:
1. In column A starting at row 5, pull in the timestamp values from the first imported file sheet (copy sheet "11" range A5:A15000). The first imported sheet will always be workbook sheet 3 position, but the sheet name may change with each import. For this example the sheet is currently called "11".

2. In column B starting at row 5, pull in the sampling value from the first imported file sheet (copy sheet "11" range B5:B15000). Populate cell B1 with the sheet name "11" in our example (workbook sheet 3 position).

3. In column C, pull in sampling values from the second file sheet "12" (workbook sheet 4 position), range B25:B15000 if the sheet exists. Populate cell C1 with the sheet name "12" (workbook sheet 4 position).

4. In column D, pull in sampling values from the third file sheet "13" (workbook sheet 5 position), range B25:B15000 if the sheet exists. Populate cell D1 with the sheet name "13" (workbook sheet 5 position).

Continue the data copying from all imported sheets as above, populating the columns going across as needed.

Thanks in advance for any looping code that I could modify and append to my import button (not yet added to this template).

 

[Fluff]

How about

Sub CopyCols()

   Dim Cnt As Long
   
   With Sheets("Summary")
      Sheets(3).Range("A5:B15025").Copy .Range("A5")
      .Range("B1").Value = Sheets(3).Name
      For Cnt = 4 To Sheets.Count
         Sheets(Cnt).Range("B5:B15025").Copy .Cells(5, Cnt - 1)
         .Cells(1, Cnt - 1).Value = Sheets(Cnt).Name
      Next Cnt
   End With
End Sub

 

[GetSmart]

WOW...that little bit of code saved me TONs of work and frustration. Thank you so much for the example!!!!!!!

 

[Fluff]

Glad to help & thanks for the feedback