Macro code

  • Thread starter Thread starter Shu of AZ
  • Start date Start date
S

Shu of AZ

In the example below, I am trying to go from a worksheet BOOK1.xls to another
worksheet already open Tup071130_hr.csv and copy then go back to BOOK1.xls
and paste the contents.
The worksheet i want to go to will be named something different each day but
end with .csv and will have 12 preceeding characters. Since it changes daily
I need to use a wildcard for the window name. I can't get a wildcard of any
type to work. Can anyone tell me how it should be worded in the macro to see
the open and new worksheet when it is open each day? Thanks in advance.

Windows("????????????.csv").Activate
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
ActiveSheet.Paste
 
Copy from first loaded workbook (1) to active workbook

Sub xCopy()
Workbooks(1).Sheets(1).Cells.Copy ActiveWorkbook.ActiveSheet.Cells
End Sub


"Shu of AZ" skrev:
 
The first loaded workbook changes names daily and there may be many other
workbooks open. Only one will have the .csv extension
 
The best way to switch back-and-forth bedtween a new workbook and the active
workbook is like this

workbooks.open myfilename
set wbk = activeworkbook 'this is the recently opened book
Thisworkbook.activate 'selects the workbook where the macro is located
wbk.activate 'back to the opened workbook
 
wildcard for workbooks with an extension of csv?

Shu of AZ said:
The first loaded workbook changes names daily and there may be many other
workbooks open. Only one will have the .csv extension
Click to expand...
 
Sub findWB()
For Each wb In Workbooks
If Right(wb.Name, 3) = "csv" Then csvBook = wb.Name
Next
Workbooks(csvBook).Sheets(1).Cells.Copy ActiveWorkbook.ActiveSheet.Cells
End Sub


"Shu of AZ" skrev:
 
Thanks excelent,
When I place this into the macro I use, it returns an error stating and
referring to the line, For Each wb In Workbooks, "variable not defined" and
highlighting 'wb'.
Would that be a DIM statement and if so what type, string???? etc.
 
A CSV file when opened in Excel looks like a workbook. It is always a good
idea immediately after opening a new workbook to set a variable to the
activeworkbook so you can easily switch back and forth between different
workbooks.
 
Back
Top