?
Solved

copy/paste macro excel

Posted on 2014-02-05
14
Medium Priority
?
285 Views
Last Modified: 2014-02-06
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
Comment
Question by:jfrank14
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
14 Comments
 
LVL 19

Expert Comment

by:regmigrant
ID: 39837367
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
 
LVL 31

Expert Comment

by:gowflow
ID: 39837377
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
 
LVL 19

Expert Comment

by:regmigrant
ID: 39837383
and that's why he/she is the genius
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:jfrank14
ID: 39837417
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
 
LVL 19

Expert Comment

by:regmigrant
ID: 39837470
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
 
LVL 31

Expert Comment

by:gowflow
ID: 39837526
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
 
LVL 19

Expert Comment

by:regmigrant
ID: 39837536
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
 
LVL 31

Expert Comment

by:gowflow
ID: 39838256
@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
 
LVL 19

Expert Comment

by:regmigrant
ID: 39838321
as my ex-wife says - is only flattery if its not true :)
0
 

Author Comment

by:jfrank14
ID: 39838722
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
 
LVL 31

Expert Comment

by:gowflow
ID: 39838965
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
 
LVL 31

Accepted Solution

by:
gowflow earned 2000 total points
ID: 39839003
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
 

Author Closing Comment

by:jfrank14
ID: 39839818
awesome work! worked like a charm! thanks!
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39839852
your welcome.
gowflow
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
New style of hardware planning for Microsoft Exchange server.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question