For Each to export pages

[crburke92]

I have a workbook that is acting as a database. Every time we get results back I can easily upload everything to a worksheet named database, and then recall all data into a nicely organised table on my worksheet named OilSamplingResults by typing in a units serial number on the results page. I'm trying to setup a page called ExportList, wherein I can type the unit serial numbers in column A, and the equipment ID in column B. The macro copies the serial number into my results page to obtain all the numbers, copies the page (now called OilSampleResults (2)), 'Flattens' the newly created page by doing a special paste of just values, and then moves the sheet to a different workbook called TestFile, and renames the worksheet based on the Equipment ID column.

Sub ExportData()

Dim Database As Workbook
Dim TestFile As Workbook
Dim OilSamplingResults As Worksheet
Dim ExportList As Worksheet

ScreenUpdating = False

Sheets("ExportList").Range("A2").Copy Sheets("OilSamplingResults").Range("G5")
    Sheets("OilSamplingResults").Copy After:=Sheets("OilSamplingResults")
        Sheets("OilSamplingResults (2)").Range("A1:BN45").Select
            Selection.Copy
             Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
             :=False, Transpose:=False
                Sheets("OilSamplingResults (2)").Move After:=Workbooks("TestFile").Sheets("Master")
Workbooks("TestFile").Sheets("OilSamplingResults (2)").Name = Workbooks("Database").Sheets("ExportList").Range("B2")
Workbooks("Database").Sheets("ExportList").Activate
ScreenUpdating = True

End Sub

This portion works fine but I'm having two main issues.

The export to workbook ideally could be a variation of names based on job (My general format is 'report name - client - brief job description'). That being said, when I hit export, it only needs to go to that one file, but the next job and group of samples will be a different file name. Not sure if there's a way I could simply have a "File Name" spot at the top of my ExportList page that replaces "TestFile" in my code above. I've tried a few ways to dim a variable of sorts but my VBA is only so-so and I keep getting errors.

The second problem is For Each loops. Any given job could have between 1-60 pieces of equipment being sampled. My hope is to be able to be able to just type in the serial numbers and ID's once.

 

[crburke92]

Bump

 

[crburke92]

Bump

 

[crburke92]

I've made some progress...

Sub ExportAllSheets()

Dim DB As Workbook
Dim TF As Workbook
Dim OSR As Worksheet
Dim EL As Worksheet
Dim SN As Range
Dim List As Range
Dim Name As Range

Set DB = Workbooks("Database")
Set TF = Workbooks("TestFile")
Set OSR = DB.Sheets("OilSamplingResults")
Set EL = DB.Sheets("ExportList")
Set List = EL.Range("A2:A24")
Set Name = EL.Range("B2:B24")

ScreenUpdating = False

For Each SN In List

EL.Range("A2").Copy OSR.Range("G5")
    OSR.Copy After:=OSR
        Sheets("OilSamplingResults (2)").Range("A1:BN45").Select
            Selection.Copy
             Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
             :=False, Transpose:=False
                Sheets("OilSamplingResults (2)").Move After:=TF.Sheets("Master")
TF.Sheets("OilSamplingResults (2)").Name = Workbooks("Database").Sheets("ExportList").Range("B2")
Workbooks("Database").Sheets("ExportList").Activate
ScreenUpdating = True

Next

End Sub

When I run this, I get a name already exists error. I have the ranges set to hard cell values A2 and B2 to copy and use as the new sheets name. To have the second loop use A3 and B3....4,5,6 and so on, do I need to do something like...

Dim I as Integer
For i + 1 = Something...

Any help is much appreciated!

 

[crburke92]

Figured it out myself in the end! Here's the solution for anyone in the future

Sub ExportAllSheets()

Dim DB As Workbook
Dim OSR As Worksheet
Dim EL As Worksheet
Dim SN As Range
Dim List As Range
Dim Name As Range
Dim x As String
Dim i As Long

Set DB = Workbooks("Database")
Set OSR = DB.Sheets("OilSamplingResults")
Set EL = DB.Sheets("ExportList")
Set List = EL.Range("A2:A100")
Set Name = EL.Range("B2:B100")

x = InputBox("Enter File Name")

ScreenUpdating = False

For Each SN In List
For i = 2 To 100
If EL.Range("A" & i) = "" Then GoTo SheDone
EL.Range("A" & i).Copy OSR.Range("G5")
    OSR.Copy After:=OSR
        Sheets("OilSamplingResults (2)").Range("A1:BN45").Select
            Selection.Copy
             Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
             :=False, Transpose:=False
                Sheets("OilSamplingResults (2)").Range("G5:N5").Merge
                    With Sheets("OilSamplingResults (2)").Range("G5:N5").Borders
                        .LineStyle = x1Continuous
                        .Color = vbBlack
                    End With
                   Sheets("OilSamplingResults (2)").Move After:=Workbooks(x).Sheets("Master")
Workbooks(x).Sheets("OilSamplingResults (2)").Name = Workbooks("Database").Sheets("ExportList").Range("B" & i)
Workbooks("Database").Sheets("ExportList").Activate
ScreenUpdating = True
Next
Next

SheDone:
        MsgBox ("Export Complete")
End Sub

Cheers all