Use partial ws names Copy data btw wkbs

[Rowland Hamilton]

Folks:

I can't seem to get this code to work. I received an 1004 error eventually, indicating it does not like my method of selecting the the worksheets in the source workbook.

I want to cycle through the worksheets in the workbook ("United States (de linked).xlsm") and

select only the ws with tab names that include "*-Line item*" (which is preceded by a cc # and sometimes followed by an s as in "5049-Line Items")

Then copy a contiguous, but varing range of data from each sheet and

paste it to a master worksheet in the workbook named "Line items-Combined.xlsm" which contains the macro.

Here is the code

Sub Populate_line_item_workbooka()
Dim MasterWB As Workbook
Dim SourceWB As Workbook
Dim ws As Worksheet
Set MasterWB = Workbooks("Line items-Combined.xlsm")
Set SourceWB = Workbooks("United States (de linked).xlsm")
For Each ws In SourceWB.Worksheets
If ws.Name Like "*-Line item" Then
ws.Select
Range("A3").Select
Call Copy_move_line_items
Exit For
End If
Next ws
End Sub
Sub Copy_move_line_items()
'copy
Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

'paste
Windows("Line items-Combined.xlsm").Activate
Sheets("Master-Incoming").Activate
Range("A65000").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

Thank you
Rowland

 

[Rowland Hamilton]

Copy data from 1 workbook to another, partial tab names

<TABLE cellSpacing=0 cellPadding=2 width="100%" border=0><TBODY><TR><TD align=left>Pritchard from egghead cafe gave me this answer but I doesn't quite work for me bc my cost centers are varying characters long, alpha and numeric so his mid formula doesn't work. How can I substitute a wildcard formula with Like and * instead? Thanks:


Hi Rowland,

Attachment for you--->SearchByPartial&MoveData.zip


Some sequence of the procedure are not correct...

------------------------------------------------------

Sub Populate_line_item_workbooka()
  
Dim MasterWB As Workbook
Dim SourceWB As Workbook
  
Dim ws As Worksheet
  
Set MasterWB = Workbooks("Line items-Combined.xlsm")
'we need to let excel know the Work book then we can define it as SourceWB
Workbooks.Open Filename:=ThisWorkbook.Path & "\" & "United States (de linked).xlsm"
Set SourceWB = Workbooks("United States (de linked).xlsm")
 
SourceWB.Activate
For Each ws In SourceWB.Worksheets
  'MsgBox (Mid(ws.Name, 5, 10))
'Here I am not sure of like *-Line Item* so  I change to be  If Mid(ws.Name, 5, 10) = "-Line Item" Then
    If Mid(ws.Name, 5, 10) = "-Line Item" Then
      ws.Select
      Range("A3").Select
   ' the sub is only short one, so no need to splite it into 2 subs, otherwise we need to activate the  Windows("Line items-Combined.xlsm") again before redefine it as SourceWB, then we can use SourceWB
    'copy
      Range("A3").Select
      Range(Selection, Selection.End(xlDown)).Select
      Range(Selection, Selection.End(xlToRight)).Select
      Selection.Copy
  
      
    'paste
    MasterWB.Activate
      Sheets("Master-Incoming").Activate
      Range("A65000").Select
      Selection.End(xlUp).Select
      ActiveCell.Offset(1, 0).Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  
      SourceWB.Activate
    'Exit for...No need to be Exit for here, for each......then will loop until last object in for is performed then it will stop itself.
    End If
Next ws
  MasterWB.Activate
End Sub

-------------------------------------

Pichart Y.
</TD></TR></TBODY></TABLE>

 

[Norie]

Re: Copy data from 1 workbook to another, partial tab names

Rowland

Are you sure the problem is with the use of a wildcard to identify the worksheet you want to get data from?

 

[Rowland Hamilton]

Closer to solution, here is my latest, final help with closing the loop or scrutinizing my Like formula?:

Pitchard:

Substituting:

 If ws.Name Like "*-Line item*" Then

<CODE style="COLOR: #000"></CODE>
<CODE style="COLOR: #000">for</CODE>

 If Mid(ws.Name, 5, 10) = "-Line item" Then

<CODE style="COLOR: #000"></CODE>
<CODE style="COLOR: #000"></CODE>

<CODE style="COLOR: #000">I was able to get the macro to cycle through all the sheets and copy the data but when it at the end it gets a compile error, I assume because it doesn't know to stop looking:</CODE>
<CODE style="COLOR: #000"></CODE>
compile error 1004 - Method selection of object worksheet failed. and debug gives me a yellow arrow at the code

ws.Select

right after

If ws.Name Like "*-Line item*" Then

Thank you - Rowland


Here is complete code:

Sub Populate_line_item_workbooka()
  
Dim MasterWB As Workbook
Dim SourceWB As Workbook
  
Dim ws As Worksheet
  
Set MasterWB = Workbooks("Line items-Combined.xlsm")
Application.DisplayAlerts = False
'we need to let excel know the Work book then we can define it as SourceWB
Workbooks.Open Filename:=ThisWorkbook.Path & "\" & "United States (de linked).xlsm", _
UpdateLinks:=0
Set SourceWB = Workbooks("United States (de linked).xlsm")
'ActiveWorkbook.Names("MyRange").Name  Like "*!*"
 
SourceWB.Activate
Application.DisplayAlerts = False
For Each ws In SourceWB.Worksheets
  'MsgBox (Mid(ws.Name, 5, 10))
'Here I am not sure of like *-Line item* so  I change to be  If Mid(ws.Name, 5, 10) = "-Line item" Then
    'If Mid(ws.Name, 5, 10) = "-Line item" Then 'note: Mid formula won't work bc varying number characters for cc
   If ws.Name Like "*-Line item*" Then
        ws.Select
        Range("A3").Select
   ' the sub is only short one, so no need to splite it into 2 subs, otherwise we need to activate the  Windows("Line items-Combined.xlsm") again before redefine it as SourceWB, then we can use SourceWB
    'copy
        Range("A3").Select
        Range(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Copy
  
        
    'paste
        MasterWB.Activate
        Sheets("Master-Incoming").Activate
        Range("A65000").Select
        Selection.End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  
        SourceWB.Activate
        'Exit for...No need to be Exit for here, for each......then will loop until last object in for is performed then it will stop itself.
    End If
Next ws
  MasterWB.Activate
End Sub

Right before this message I responded to Pritchard with this:

Pitchard: Thank you.

I got your sample to work and couldn't figure out why my data wouldn't,.

First, I thought it was because "-Line Item" had a capital "I" in "Item" in some of your code while all my sheets have lower case "i", so I changed all occurences in the code to match.

Second, I realized none of my tabs with "-Line Item" in the middle of the name have it start 5 characters in, they all start at different points with different lenght cost centers, hence trying to use the wild card.

Perhaps I can make it work with the wildcard again (like *-Line item*). I'll let you know if I solve it. In the mean time, let me know if you have any suggestions.

I may also have to do a similar exercise where I pull data from all the tabs that don't include "line item" or "summary" or "HC", which is why I figured if Like works I can use Not Like and And for the others.


I didn't realize some one responded because I didn't get an e-mail update. I haven't been on the forums in a while.

Thanks - Rowland

 

[Rowland Hamilton]

Re: Copy data from 1 workbook to another, partial tab names

Norie: As you can see from previous post, I don't think Like was the problem. I think originally I didn't identify both workbooks becore defining them. Now, I maybe having a problem with closing my loop, since all my data is being copied to the master tab in the master work book but gets an error trying to select the next worksheet after all have been selected. - Thank you, Rowland

 

[Norie]

Re: Copy data from 1 workbook to another, partial tab names

Rowland

That wasn't really what I meant.

The problem I think is more to do with using Select/Activate and unqalified references.

Select/Activate isn't needed for what you want to do and if you don't qualify references correctly it's almost inevitable you will run into problems.

 

[Rowland Hamilton]

Norie:

Thank you for you r help.

Not sure what you mean, how should I better qualify my references in this code?

So far, I attempted to identify 2 open workbooks by exact name and define them as SourceWB and MasterWB, then used activate/Select codes as I travel between them.

I can see how a problem could arise but I have to pull the data from 1 workbook to another so I'm not sure how else to automate it with vba code as simple as possible.

Thank you - Rowland

 

[Rowland Hamilton]

Folks:

I ended up using an "On Error GoTo ErrorCatch" and "ErrorCatch:" solution because I think it just didn't know when I ran out of worksheets (I could be wrong). Probably better to at least have the MsgBox tell the real error with

MsgBox Err.Description

but I just made it tell me

MsgBox ("No More Sheets To Copy") 'Err.Description

Here is my final code:

Sub Populate_line_item_workbooka()
  
Dim MasterWB As Workbook
Dim SourceWB As Workbook
  
Dim ws As Worksheet
  
Set MasterWB = Workbooks("Line items-Combined.xlsm")
Application.DisplayAlerts = False
'we need to let excel know the Work book then we can define it as SourceWB
Workbooks.Open FileName:=ThisWorkbook.path & "\" & "United States (de linked).xlsm", _
UpdateLinks:=0
Set SourceWB = Workbooks("United States (de linked).xlsm")
'ActiveWorkbook.Names("MyRange").Name  Like "*!*"
 
SourceWB.Activate
Application.DisplayAlerts = False
On Error GoTo ErrorCatch
For Each ws In SourceWB.Worksheets
  'MsgBox (Mid(ws.Name, 5, 10))
'Here I am not sure of like *-Line item* so  I change to be  If Mid(ws.Name, 5, 10) = "-Line item" Then
    'If Mid(ws.Name, 5, 10) = "-Line item" Then 'note: Mid formula won't work bc varying number characters for cc
   If ws.Name Like "*-Line item*" Then
        ws.Select
        Range("A3").Select
   ' the sub is only short one, so no need to splite it into 2 subs, otherwise we need to activate the  Windows("Line items-Combined.xlsm") again before redefine it as SourceWB, then we can use SourceWB
    'copy
        Range("A3").Select
        Range(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Copy
  
        
    'paste
        MasterWB.Activate
        Sheets("Master-Incoming").Activate
        Range("A65000").Select
        Selection.End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  
        SourceWB.Activate
        'Exit for...No need to be Exit for here, for each......then will loop until last object in for is performed then it will stop itself.
    End If
    
Next ws
  
  MasterWB.Activate
ErrorCatch:
    
  SourceWB.Activate
  Application.CutCopyMode = False
  Range("A1").Select
  
  MasterWB.Activate
  Range("A1").Select
  
  MsgBox ("No More Sheets To Copy") 'Err.Description
End Sub

Thank you - Rowland