Copy columns if the sheet name in source WB matches sheet name in target WB

  • Hello,


    I know this must sound childish to VBA pros, but I am trying to automate data import in my workbook. In a nutshell, I'm trying to write code that will:


    1) let the user select the source file (already figured out this part);
    2) if one of the sheets in the selected source workbook matches the name of the sheet in my target workbook, copy columns A:S and paste them into my target workbook


    I've got the following code which works fine but I must specify sheet names for each individual sheets that need to be imported (so, basically repeat the same passage of code over and over again):


    Code
    For Each ws In ActiveWorkbook.Worksheets
    If ws.Name = "Sheet1" Then
                ActiveWorkbook.Sheets("Sheet1").Range("A:S").Copy Destination:=ThisWorkbook.Sheets("Sheet1").Range("A:S")


    Ideally, I want something along the following lines:


    Code
    For Each ws In ActiveWorkbook.Worksheets
        If ActiveWorkbook.ws.Name = ThisWorkbook.ws.Name Then
              ActiveWorkbook.ws.Range("A:S").Copy Destination:=ThisWorkbook.ws.Range("A:S")
    Next



    Doesn't need to be a ready solution, but maybe you can point me out in the right direction? Thanks!

  • Re: Copy columns if the sheet name in source WB matches sheet name in target WB


    Thanks pike, I'd actually like to keep the ActiveWorkbook/ThisWorkbook references as the name of the import file is not static (I've got 10 WBs that I will be importing, so this seemed like the best way to go about it). Here's the full code, I've just stripped it of checks and messages that I use to check if the target WS is empty etc.:



  • Re: Copy columns if the sheet name in source WB matches sheet name in target WB


    Hi Halvar,
    working with your code try

  • Re: Copy columns if the sheet name in source WB matches sheet name in target WB


    pike, thanks a lot for taking the time to go through the code. The code I posted works fine, what I am actually trying to automate is the If ws.Name = ... Else If routine. Right now I need to repeat the same chunk of code for each new worksheet - right now that's 10 different worksheets.


    Instead of specifying the whole "if worksheet name in this workbook matches the worksheet name in the active workbook then copy data.." thing I want to create a loop that will go through all worksheets in both WB and compare them. If the name matches, I want the data to be copied over. Have you ever had to tackle a problem like this before?

  • Re: Copy columns if the sheet name in source WB matches sheet name in target WB


    no, you could use a loop with in a loop .. very slow

  • Re: Copy columns if the sheet name in source WB matches sheet name in target WB


    thank you sir! you're a genius. this works perfectly, I've just had to declare a new variable and tweak the next statement. I know this must be not the most efficient solution, but since I'm not working with hundreds of tabs I think it's acceptable.


    here's the final code in case anyone else needs it:


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!