Copy worksheet based on condition to other workbook

[mogambo]

Hello dear

This is my first post so please bear with me if I happen to not follow the policies of this forum unknowingly. Thank you.

my question

I have a workbook called "Dumps". I have over 30 worksheets in this workbook. I have another workbook called "Channel Master" containing 3 worksheets. What I want to do is, if a sheetname in the "Dumps" workbook matches a cell value of "Channel Master" on sheet number 1, then copy the specific range which I define, presently in "Dumps", from row 2 to "Channel Master's" sheet number 3, row 3.

I know that the following code would be required to accomplish this task but how do I form an IF() statement in VBA and write the code to accomplish the above requirement ?

Code Line: ActiveSheet. Name = ActiveSheet.Range("A1").Value

Could someone help me with some pointers please ?

[GTO]

Greetings mogambo,

As to "following policies", of course you should familiarize yourself with the rules, which can be found found under the FAQ button at top. In general though, common courtesies, providing a thread link if/when cross-posting, and giving decent descriptions of the problem or goal will certainly gain others help.

Please let me take the opportunity to be the first to greet you here and say "Welcome!". Although I have only been a member a couple of months, I was a visitor for a couple of years, and can categorically state that you will 'meet' a lot of very nice folks here, who will go out of their way to help.

Now as to your question: your current line of code would simply rename the active sheet to whatever is in cell A1 of the active sheet.

For instance, if the sheet was currently named, "Sheet1" on the tab at the bottom, and the current value of cell A1 was "My Sheet", then the code would simply change the tab's caption to "My Sheet".

Now the below would check Sheet 1, but I doubt its much help. See if you can post examples of both workbooks (examples should NOT include any private/company information), or please describe more specifically where everthing is at, such as what cell on sheet 1 of Channel Master are we trying to find a match for?

[vba]Option Explicit
Sub fake()
Dim wksWorksheet As Worksheet
For Each wksWorksheet In Workbooks("Dumps.xls").Worksheets

If wksWorksheet.Name = ThisWorkbook.Worksheets(1).Range("A1") Then
MsgBox "I found the sheet!" '<--- do stuff here, like copy

Exit Sub
End If
Next

End Sub[/vba]

Hope this gets you started in the right direction, and again, welcome and nice to 'meet'

Mark

[mogambo]

Thank you for the warm welcome, sir.

Yes, I am familiar with the rules of discussion forums and I agree to abide by them.

For my question and your answer:

No. I do not want to rename the worksheet. I just saw that line of code somewhere and I thought it would help me with my requirement and so I posted it here asking for help.

The code which you posted, I think it will atleast get me started with my work. I was looking forward to start my work with writing an IF() condition in VBA which I don't know properly. I can say, I am not bad with Excel which does not involve VBA.

I will try this and tell you about it later because right now I am in a cybercafe. I can post the 2 workbooks but not right now as I do not have access to them. I will also provide more explanations so that you guys can help me in a better manner. Thanks very much for your help.

Also if you can show some pointers about how do I go about writing code for copying the range from 1 workbook to another workbook (pasting on a different sheet other than / basis where the condition or the criteria is checked for) ? Should I record a macro ?

But again it can mess up with the Ranges part which I am afraid !

[GTO]

Hi mogambo,

...Also if you can show some pointers about how do I go about writing code for copying the range from 1 workbook to another workbook (pasting on a different sheet other than / basis where the condition or the criteria is checked for) ?

In reading your post, it appeared to me that you would like a small code snippet to get you started. The below checks cell A1 of Sheet 1 in Channel Master, for the name of the sheet in Dumps. This covers your first IF statement, and IF the sheet is found, it copies part of the second row, specifically: the range of "A2:I2" to the destination you indicated.

[vba]Sub EX_CopyPasteSpecial_BothWBsOpen()
Dim wksWorksheet As Worksheet

For Each wksWorksheet In Workbooks("Dumps.xls").Worksheets

If wksWorksheet.Name = ThisWorkbook.Worksheets(1).Range("A1") Then

wksWorksheet.Range("A2:I2").Copy ThisWorkbook.Worksheets("Sheet3").Range("A3")

Exit Sub
End If
Next

End Sub[/vba]

Should I record a macro ?

I personally use this feature a lot. The caveat however is that my code rarely ends up very similar to the recording. This is simply because recording is quite literally recording everything you do. After a bit, you will begin to see that Activating and Selecting anything is rarely advantageous.

Now disregarding that for a moment, I think that recording is a great way to 'lay down' what I'm trying to do. For instance, if I wanted to find a certain value, and I expect to find the value somewhere in row two, I might want to perform a Find operation. If I record it, I get:

[vba]Cells.Find(What:="to", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _
, SearchFormat:=False).Activate[/vba]

(red added for clarity) Now it's doubtful that I want to activate the cell, but I might want to set this cell as a range to operate off of, such as:

[vba]Dim rngMyRange As Range

Set rngMyRange = Cells.Find(What:="to", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _
, SearchFormat:=False)

If Not rngMyRange Is Nothing Then
rngMyRange.Offset(2, 0).Value = "the store I need to go"
End If[/vba]

Now if the running macro finds "to", it enters "the store I need to go" two cells below.

So as you can see, macro recording can be a great way to learn and/or perform basic tasks, but in my opinion, most often it gives you a "draft" to start building from.

But again it can mess up with the Ranges part which I am afraid !

Not sure what this meant, but if you are referring to overwriting ea time the macro is run, you are correct.

Once you post example workbooks (If I forgot to mention, you can get around the one attachment limit, by zipping both wb's), we'll have a better view of the path ahead.

Mark

[mogambo]

Hi Mark

Thanks heaps for helping me out. I have tweaked the code which you provided, to suit my needs and I have it all set and ready. Everything seems to be nice and working.

However, I wanted to ask you about the IF() condition used in the macro to check for the criteria.

It seems that the macro does not like if the cell value (which is checked with the sheetname) is entered in lowercase characters. The Sheetnames in "Dumps.xls" are alphanumeric and uppercase. When I enter the code in lowercase in the specific cell of "Channel Master.xls", the macro does not work. However it works when the alphanumeric code I enter is uppercase characters ?

I suppose, this could be solved with the use of UCase() function but I do not know how to use this function.

I was thinking, you might want to see the code, which I tweaked and am using it to my needs:

Option Explicit

Sub Ex_Dumping()
    
    
    With Application
    
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .EnableEvents = False
        
    End With
    
    Dim wksWorksheet As Worksheet
     
    For Each wksWorksheet In Workbooks("Dumps.xls").Worksheets
         
        If wksWorksheet.Name = ThisWorkbook.Worksheets("SS").Range("A4") Then
             
            wksWorksheet.Range("A3:AJ1000").Copy ThisWorkbook.Worksheets("SS-DUMP").Range("A3")
             
            Exit Sub
        
        End If
    
    Next
    
    With Application
    
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
    .EnableEvents = True
       
    End With
     
End Sub

Can you please help me with this case sensitivity problem ?

Mark, thanks for all your inputs.

[GTO]

Hey there,

Try this:
[vba]'//for: //
'If wksWorksheet.Name = ThisWorkbook.Worksheets("SS").Range("A4") Then

'//Substitute: //
If UCase(wksWorksheet.Name) = _
UCase(ThisWorkbook.Worksheets("SS").Range("A4").Value) Then
'...statements...[/vba]

Either UCase or LCase, that way you are comparing apples to apples...

Hope this helps,

Mark

[mogambo]

thank you :-) Problem solved !