One macro to open multiple workbooks and sheets issue

**Darkcloud617** · 10-17-2019, 05:01 PM

Hello,

I just wanted to Thank You in advance for reading and possibly helping me. I have some knowledge of VBA but I have issues figuring out the intricacies unless I am modifying an already existing code. I am learning.

I have been trying to get a macro that will open a different workbook, find the sheet, insert a blank range, copy data from row above and paste it is a value in the newly created range. This macro will need to repeat this in about 11 workbooks with multiple sheets in each. I have attempted to do this with macro recorder and creating a few objects but I am running out of luck when it attempts to switch to the next workbook and change sheets. Above that... I know there must be a better way as this seems to be very manual and the code will be super long when its done.

I have included a copy of a mock up for what I am attempting. It consists of 3 workbooks. The main workbook has the macro and the other two will be the ones modified. Again, it will need to do this with about 11 workbooks in the end. Changing the VBA script to the location you saved them should work and you can see what my problem is.

Any help at all would be very very appreciated. I am learning but I am still using macro recorder as my primary means which is probably the issue in itself.

Thank you for reading.

**AlphaFrog** · 10-17-2019, 07:07 PM

Are the filenames of the 11 workbooks known and constant, or do you want to loop through all files in a folder, or look for file names with a base name and number e.g. File01.xlsx, File02.xlsx...File11.xlsx, or prompt the user to select the files ?

Same for the worksheets in each file. Do you want to loop through all sheets, or a list of specific ones, or ones that have a base name?

Sub MoveDataOnOtherSheets()
'
' MoveData Macro
' this must repeat for 11 different worksheets
    
    
    Dim wb        As Workbook
    Dim ws        As Worksheet
    Dim vFile     As Variant
    Dim strPath   As String
    Dim counter   As Long
    
    strPath = ThisWorkbook.Path & "\"
    
    Application.ScreenUpdating = False
    
    For Each vFile In Array("Testwb", "Testwb2")
        
        Set wb = Workbooks.Open(strPath & vFile)
        
        For Each ws In wb.Worksheets    ' loop through all sheets
            'Insert, copy, paste
            ws.Range("L5:W5").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            ws.Range("L5:W5").Value = ws.Range("L4:W4").Value
        Next ws
        
        wb.Close SaveChanges:=True
        counter = counter + 1
        
    Next vFile
    
    Application.ScreenUpdating = True
    MsgBox counter & " files processed.", vbInformation, "Move Data Complete"
    
End Sub

**Darkcloud617** · 10-18-2019, 08:36 AM

Thank you for sending this. The sheets are based off of the names, essentially the first sheet does not need to be included. No reason for that code to be reworked though. It works super fast and I should have no problem making it work. Thank you so much. You saved me tons of frustration and time with a much better code.

I do have a question though just for my own understanding of VBA. "strPath = ThisWorkbook.Path & "\"" What does this actually mean? Is this a way to define a workbook that will be referred to multiple times? What does the "\" represent?

I get confused on what a string represents and how to define them properly. Thank you again for the assistance in the code. I am impressed and it is extremely helpful for me.

**AlphaFrog** · 10-18-2019, 09:57 PM

You're welcome.

ThisWorkbook is a keyword that references the workbook that this code is within.

ThisWorkbook.Path returns the path (folder) that ThisWorkbook is in. I assumed all the other workbooks were in the same folder e.g.
C:\MyFolder\MySubfolder

Adding a path separator "\"at the end of the path allows you to add a file name at the end which the code later does.
C:\MyFolder\MySubfolder\Filename.xlsx

A string is programming-speak meaning text. I think the etymology comes from some math term; a string of symbols.

One macro to open multiple workbooks and sheets issue

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

One macro to open multiple workbooks and sheets issue

Re: One macro to open multiple workbooks and sheets issue

Re: One macro to open multiple workbooks and sheets issue

Re: One macro to open multiple workbooks and sheets issue

Thread Information

Users Browsing this Thread

Similar Threads

Need Macro that copies Sheets to 3 different open excel workbooks and saves it

vba to copy all sheets in the workbooks open by macro to active workbook

[SOLVED] Macro to open multiple workbooks and delete specific sheets

[SOLVED] how to use macro (split sheets/tab) to all open workbooks

Application.Ontime issue running macros in wrong spreadsheet with multiple sheets open

[SOLVED] Run Macro on Multiple OPEN Workbooks

macro to open multiple workbooks at once

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions