A Few Simple VB Questions w/ multiple workbooks

[campbbri]

Hi everyone. I'm building a macro that performs a lot of functions, but for one area in particular I need your help. Mostly I don't know the right syntax for this step.

The first workbook (call it "File1.xls") has only one worksheet called "Inputs". Based on a few user settings, it opens a target workbook ("File2.xls") with an unknown number of worksheets and unknown worksheet names.

I want to add these worksheets to File1.xls in the same order with the same names, copy the formatting (but not the data) to each newly created worksheet, and finally read in the number of rows and columns that are actually used in each worksheet.

I can add the correct number of worksheets using the code below, and I can read the worksheet names. Unfortunately the new worksheets get added to the left of the inputs tab, while I want them to be added to the right. Also, I can't figure out how to apply the names to the newly created worksheets. Finally, I don't know how to copy the formats over or read the number of used rows and columns in each worksheet.

Workbooks.Open FileName:="C:\Path\File2.xls", ReadOnly:=True

Windows("File1.xls").Activate

For Each Sheet In Workbooks("File1.xls").Worksheets
    Worksheets.Add
    WorkSheetName = Sheet.Name
Next Sheet

Thanks!

 

[xenou]

1) You can simply copy the sheets...don't need to add a new sheet and then copy onto it. Perhaps the only reason not to use the copy method is if you want to not copy code on the sheets when you make the copy.

2) Use the index of the worksheets. The first sheet is worksheet(1), the next is worksheet(2), and so on.

3) I copy them in backwards to maintain the same order

4) I don't see what you want done with the used columns and rows. What I did here was to clear the cell contents (same as delete in native Excel) - this would preserve the formatting on the sheet.

5) you will error out if any sheet has a name the same - or get a (1) thrown in to the name by Excel. If this is a danger or a problem, you will want to add a test at the beginning of the routine to check for name duplicates.

Sub test()
Dim wb As Workbook, wb2 As Workbook
Dim intCountSheets As Integer, intInputsIndex As Integer

'wb2 is where the code runs and sheets are imported to
Set wb2 = ThisWorkbook

'wb is where the sheets are copied from
Set wb = Workbooks("test.xls")

'Get worksheet index of "Inputs" - sheets will be copied to the right
intInputsIndex = wb2.Worksheets("Inputs").Index

'copy sheets
intCountSheets = wb.Worksheets.Count
For x = intCountSheets To 1 Step -1
    
    'copy sheet
    wb.Worksheets(x).Copy After:=wb2.Worksheets(intInputsIndex)
    
    'Clear contents - preserves formatting
    ActiveSheet.Cells.ClearContents

Next x

Worksheets("Inputs").Activate

End Sub

Will this help you?

 

[campbbri]

Alexander,

Thank you so much for your help. I really appreciate you taking the time to write out the code for me.

This gets me most of the way there, but I don't think I was desciptive enough regarding the columns and rows. I didn't want to get into the long and unecessary details, but essentially I will be replacing the deleted cells with a long formula. The formula will reproduce headers and text if they exist (by linking to the other sheet), and for numerical values it will make comparisons and calculations with yet other workbooks depending on the user settings in the "inputs" sheet.

The problem is I don't know the number of rows and columns used in the target workbook so I don't know what range to paste the formula over.

Thanks again.