@Convert excel sheet to VBA I have a great Excel sheet which I organised very be

Question

@Convert excel sheet to VBA

I have a great Excel sheet which I organised very beautifully and it took me 10 days to do it. Please tell me, how I convert the sheet into an empty sheet which has a macro, and when I run that macro, all the content will appear?

The reason why I need this is beacuse I have a software testing tool which can generate reports. However, the tool will only generate the reports using VBA. I can not define a template with a lot of content beforehand.

Since my Excel sheet is very intricate and complicated, I cannot simply record the actions while creating the sheet all over Again, it would be a massive task which would take 15 working days and drive me insane.

Thanks for your help!!!

Explanation / Answer

!$^&^The code below is stand-alone and complete. Although provided as a stand-alone Sub, you can take everything within it and copy it into an existing code module at the point you need to use it. Just change the two Const declarations so they hold the proper path to the other workbook (one with the sheet to be copied) and the sheet in it you need to copy.

Sub CopySheetFromAnotherWB()
'assumes you know the 'fixed/constant' path to the other workbook
'be it an internet address or local
'change as required
Const otherWBAddress = "C:UsersJLathamDesktopBook1.xlsx"
'change this to the name of the sheet that you need to copy
Const otherWSName = "Sheet1"
Dim otherWB As Workbook
Dim otherWS As Worksheet

Application.ScreenUpdating = False
Set otherWB = Workbooks.Open(otherWBAddress)
Set otherWS = otherWB.Worksheets(otherWSName)
ThisWorkbook.Activate
otherWS.Copy before:=ThisWorkbook.Worksheets(1)
'or if you want to put it at the end of the workbook
'otherWS.Copy after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
'clean up and continue with the real work
Set otherWS = Nothing
otherWB.Close False ' close without saving changes
Set otherWB = Nothing
'continue with other processing
'the copied sheet should be the active one at this point
'in this workbook.

End Sub

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.