Solved

copy/paste macro excel

Posted on 2014-02-05
14
274 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
  • 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 29

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
 

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 29

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 29

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 29

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 29

Accepted Solution

by:
gowflow earned 500 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 29

Expert Comment

by:gowflow
ID: 39839852
your welcome.
gowflow
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

758 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now