Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

copy/paste macro excel

Hi,
My code below works, however when I try and copy / paste values, it doesnt like how I have .Sheets. I have also tried fName.Sheets. But says invalid qualifier.

How can I reference the sheet (fName)  to copy/paste values?

thanks.

Code Below:

Dim fName As String, Report As Workbook
    
    Set Report = ActiveWorkbook
    
    fName = Report.Worksheets(1).Range("A11").Text & ".xlsx"
        
    If IsOpen(fName) Then
        MsgBox "Good, file opened."
        .Sheets("Input").Range("B8:O11").Copy
        .Sheets("Avg Daily Vol").Range("G5").PasteSpecial xlValues
        Application.CutCopyMode = False
        MsgBox "Values pasted successfully!"

    Else
        MsgBox "Today's report is currently not opened."
    End If

Open in new window

0
jfrank14
Asked:
jfrank14
  • 6
  • 5
  • 3
1 Solution
 
regmigrantCommented:
try adding

dim ws1 as worksheet, ws2 as worksheet


then after line 7


set ws1 = report.sheets("input")
set ws2 = report.sheets("Avg Daily Vol")

and you can use
ws1.range(... copy


ws2.range(... pastespecial etc
0
 
gowflowCommented:
try changing this
.Sheets("Input").Range("B8:O11").Copy
.Sheets("Avg Daily Vol").Range("G5").PasteSpecial xlValues


to this
Sheets("Input").Range("B8:O11").Copy
Sheets("Avg Daily Vol").Range("G5").PasteSpecial xlPasteValues

remove the point before Sheets as I do not see any With Report

then also this
fName = Report.Worksheets(1).Range("A11").Text & ".xlsx"

will put the value of the Cell A11 in sheet1 in variable fname appending ".xlsx"

gowflow
0
 
regmigrantCommented:
and that's why he/she is the genius
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jfrank14Author Commented:
regmigrant,
fName is the name of the file opened that I need to do the copy/paste, Not Report.

in changing:
Set ws1 = fName.Sheets("Input")
Set ws2 = fName.Sheets("Avg Daily Vol")

it doesn't like fName in there. How can I get this to work?

basically i'm running this macro off of a different workbook to do the work in a different workbook.

thanks.
0
 
regmigrantCommented:
you could use:-

Dim wbForCopy as workbook

set wbForCopy as workbooks(fname)

set ws1 = wbForcopy.sheets("input")  etc


but this will fail if the workbook isn't already open in which case:

set wbForCopy as workbooks.open(fname) will open it first
0
 
gowflowCommented:
Sorry but I see that your code has lots of faults !!
pls post the whole code and advise what you want to achieve and will write a working code for you.

Regards
gowflow
0
 
regmigrantCommented:
whilst I would love to get points -in the interest of getting your problem (completely) solved and learning a great deal into the bargain I would snatch gowflow's offer if I were you
0
 
gowflowCommented:
@regmigrant too much flatering here indeed. Tks a lot but all this only due to hard work and perseverance and most of all willingness to learn. Your attitude surely indicate your on the right track and its only matter of time, and trust me when you see others each one of us including myself feel soooo much ignorant and still looootss to learn !
gowflow
0
 
regmigrantCommented:
as my ex-wife says - is only flattery if its not true :)
0
 
jfrank14Author Commented:
thanks by: regmigrant. that doesnt seem to be working for me.

gowflow, appreciate the help. let me summarize and provide my current code.

basically, currently the code is working fine where i have the macro in a workbook, it checks if a workbook is open (fName), if its opened gives an OK msg, and if not, gives a not opened msg.

Now what I want to do (which I need help with), is if it's opened, I want to copy and paste values in the workbook (fName).

Current Code:
    Sub CopyPaste()
    Dim fName As String, Report As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim wbForCopy As Workbook

   Set Report = ActiveWorkbook
    
    fName = Report.Worksheets(1).Range("A11").Text & ".xlsx"
        
    If IsOpen(fName) Then
        Set ws1 = wbForCopy.Sheets("Input")
        Set ws2 = wbForCopy.Sheets("Avg Daily Vol")
        MsgBox "Good, file opened."
        
        ws1.Range("B8:O11").Copy
        ws2.Range("G5").PasteSpecial xlValues
        Application.CutCopyMode = False
        MsgBox "Values pasted successfully!"

    Else
        MsgBox "Today's report is currently not opened."
    End If
End Sub
Function IsOpen(wbname As String)


    Dim Wb As Workbook, fnd1 As Boolean
    fnd1 = False
    For Each Wb In Application.Workbooks
        If Wb.Name = wbname Then
            'MsgBox "Found open workbook"
            fnd1 = True: Exit For
        End If
    Next Wb
    IsOpen = fnd1
End Function

Open in new window

0
 
gowflowCommented:
Well don't mean to be bad but if she says
as my ex-wife says - is only flattery if its not true :)

and she is ur ex, then ... don't know how much of this is true !!
:)

@jfrank14  just saw your code looking at it will revert.

gowflow
0
 
gowflowCommented:
Well here it is for what you want to do

Sub CopyPaste()
Dim fName As String, Report As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim wbForCopy As Workbook

Set Report = ActiveWorkbook
 
fName = Report.Worksheets(1).Range("A11").Text & ".xlsx"
     
Set wbForCopy = IsOpen(fName)
If Not wbForCopy Is Nothing Then
    Set ws1 = wbForCopy.Sheets("Input")
    Set ws2 = wbForCopy.Sheets("Avg Daily Vol")
    MsgBox "Good, file opened."
    
    ws1.Range("B8:O11").Copy
    ws2.Range("G5").PasteSpecial xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
    MsgBox "Values pasted successfully!"
Else
    MsgBox "Today's report is currently not opened."
End If
End Sub

Function IsOpen(wbname As String) As Workbook

Dim Wb As Workbook
    
For Each Wb In Application.Workbooks
    If Wb.Name = wbname Then
        'MsgBox "Found open workbook"
        Set IsOpen = Wb
        Exit For
    End If
Next Wb

End Function

Open in new window



gowflow
0
 
jfrank14Author Commented:
awesome work! worked like a charm! thanks!
0
 
gowflowCommented:
your welcome.
gowflow
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 6
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now