Copy 1st and 2nd sheet from a closed workbook to currently opened workbook

[michibahn]

Hi All,

Anyone here can help on a VBA script to copy 1st and 2nd sheet from a closed workbook to currently opened workbook overwriting and contents of sheet 2 and 3?

The 1st and 2nd sheet names of the closed workbook changes dynamically so please don't rely on the sheet1 and sheet2 names. The 1st sheet of current opened workbook is static and contains the output of the sheets 2 and 3.

Appreciate your help in advance.

Thanks!

 

[michibahn]

I guess I have to make my self clear

The 1st and 2nd sheet names of the closed workbook changes dynamically so please don't rely on the sheet1 and sheet2 names.

The 1st sheet of current opened workbook that will be copied onto is static and contains all the formulas and calculations based from sheets 2 and 3 on the same opened workbook. The sheets 2 and 3 contains the contents from sheet 1 and 2 from the closed workbook

 

[Fluff]

Cross posted https://www.ozgrid.com/forum/forum/...d-workbook-to active-workbook-with-conditions

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[michibahn]

My apologies for being desperate in looking for solutions.

I'm cross posting to this site also >> https://www.ozgrid.com/forum/forum/...d-workbook-to active-workbook-with-conditions

 

[michibahn]

Ok I was able to get this code working. However, it copies all worksheets in Wb1. I just need to copy always the 1st 2 sheets in the file. Also, when the 1st 2 sheets are copied, I need to overwrite the sheets after sheet "00" in Wb2. Can you please help me modify the code? Thanks!

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Sub CopyAll()
Dim Wb1 As Workbook
Dim Wb2 As Workbook
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With
Set Wb1 = Workbooks.Open("c:\temp\Test.xls")
Set Wb2 = ThisWorkbook
Wb1.Sheets.Copy After:=Wb2.Sheets("00")
Wb1.Close False
With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
End With
End Sub</code>