A way to paste something twice

[phil009]

Hi everybody, I am very new to VB and this might be a really easy question but here goes-

I have taken over this project for somebody who left and had already written a lot of the code for it. Currently the macro has our data being imported into one worksheet. I was wondering if there was an easy way to make it so that it would also import this same data into another workbook. The code looks something like this...

Sub import()
Cells.Select
Selection.ClearContents
Range("A1").Select
ChDir "g:\finance\2004\analysis\compete\Cs-54\CS54 Files - Escanaba Excluded\Competitive"
Workbooks.Open FileName:="g:\finance\2004\analysis\compete\Cs-54\CS54 Files - Escanaba Excluded\Competitive\carloadsdone.XLS"
ActiveWindow.WindowState = xlNormal
With ActiveWindow
.Top = 69.25
.Left = 17.5
End With
Cells.Select
Selection.Copy
Windows("Cs54mstr (E).xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Windows("carloadsdone.XLS").Activate
With ActiveWindow
.Top = 68.5
.Left = -40.25
End With
ActiveWindow.Close
ActiveWindow.WindowState = xlMaximized
Range("A2").Select

End Sub


**Anyways, instead of the file only going to Cs54mstr (E).xls I was wondering what I would have to do to make it also go to an empty workbook called competition.xls.

Any help would be much appreciated

Thanks a lot for your time,
Phil

 

[SkipVought]

Hi,

How bout this...

Sub import()
   Dim wsThis As Worksheet, ws1 As Worksheet, ws2 As Worksheet
   Set wsThis = ActiveSheet 'I assume that this workbook is carloadsdone.XLS"
   Cells.ClearContents
   ChDir "g:\finance\2004\analysis\compete\Cs-54\CS54 Files - Escanaba Excluded\Competitive"
   Workbooks.Open Filename:="g:\finance\2004\analysis\compete\Cs-54\CS54 Files - Escanaba Excluded\Competitive\carloadsdone.XLS"
   Set ws1 = ActiveSheet
   
   With ws1.Cells
      .Copy Destination:=wsThis.[a1]
      .Copy Destination:=Workbooks("competition.xls").Worksheets("Sheet1").[a1]
   End With
   
   wsThis.Activate
   
   Set wsThis = Nothing
   Set ws1 = Nothing
   Set ws2 = Nothing
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[phil009]

Skip, thanks a lot for your help, I can't really test it until Thursday morning but the code looks great. I get back to you on Thursday and let you know how it works.

Thanks a lot Skip,
Phil

 

[phil009]

Skip (or anybody)- I was wondering if this code would work to just paste the data one more time. The part I added from my first entry is in bold.

  Cells.Select
    Selection.ClearContents
    Range("A1").Select
    ChDir "g:\finance\2004\analysis\compete\Cs-54\CS54 Files - Escanaba Excluded\Competitive"
    Workbooks.Open FileName:="g:\finance\2004\analysis\compete\Cs-54\CS54 Files - Escanaba Excluded\Competitive\carloadsdone.XLS"
    ActiveWindow.WindowState = xlNormal
    With ActiveWindow
        .Top = 69.25
        .Left = 17.5
    End With
    Cells.Select
    Selection.Copy
    Windows("Cs54mstr (E).xls").Activate
    ActiveSheet.Paste
    Application.CutCopyMode = False
    [b]Windows("competition.xls").Activate
    ActiveSheet.Paste
    Application.CutCopyMode = False[/b]
    Windows("carloadsdone.XLS").Activate
    With ActiveWindow
        .Top = 68.5
        .Left = -40.25
    End With
    ActiveWindow.Close
    ActiveWindow.WindowState = xlMaximized
    Range("A2").Select
    
End Sub

**Once again, all I am trying to do is paste the information twice instead only once to Cs54mstr (E).xls. My new file that I would like the information to go to is competition.xls

Thanks in advance,
Phil

 

[zemp]

Have you tried passing the name of the excel sheet as a parameter to the procedure. Or an array of spread sheet names then just loop through the array.

Something along these lines can allow you to copy to two or more sheets without changing your code.

Food for thought.

zemp

 

[SkipVought]

You'll find out when you test your code.

The code I posted earlier does something close to what you want without ACTIVATING and SELECTING. I had to guess at some objects, not knowing where you were starting and ending.

faq707-4105 How Can I Make My Code Run Faster?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[chiph]

Ummm, shouldn't this be in the VBA forum?

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[SkipVought]

Maybe not.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[phil009]

Sorry about that Chiph, but like I said I am brand new to VB or VBA so I just put it in this forum I did not realize the distinction.

Phil