copy and paste cell vba help

hi all,

need some help with my vba in excel if any of you could please lend your expertise.

I have a cell on worksheet: "Avg Daily Vol", A5.
The cell would contain a date. i.e. "March 16 2015".

on sheet: "Avg Daily Vol"
if column A contains the text "test data", I'd like to copy the cell from B on that row.

then i'd like to paste the values on worksheet "VS". on VS, I have a calendar where it has the day.

i.e. B8-K8 lists 1,2,3,4,5,6,7,8,9,10 (each in a different cell).
B11-K11 lists 11-20 (each different cell)
B14-K14 lists 21-30 (each different cells)
B17 lists 31.

Based on the date listed above:
worksheet: "Avg Daily Vol", A5.
The cell would contain a date. i.e. "March 16 2015".

I'd like to paste the values under it's corresponding day. So if cell contains "March 16 2015", I want to copy and paste the data on the cell UNDER G11 (which says 16 in the cell). pasting the values in G12.

I hope this isn't too complicated.

thanks!
jfrank85Asked:
Who is Participating?
 
Saurabh Singh TeotiaCommented:
When you select over wbforcopy..does it says nothing that means it's not reading the name of the workbook or not able to find that workbook in your range("A98")..try changing the format in this line..

strSourcePathname = Report.Worksheets(1).Range("A98").Text & ".xlsx"

to this..

strSourcePathname = format(Report.Worksheets(1).Range("A98").value,"mmmm dd yyyy") & ".xlsx"
0
 
Rob HensonFinance AnalystCommented:
Would be a lot simpler to understand with some sample data.

Can you upload a dummy file?

Thanks
Rob
0
 
jfrank85Author Commented:
yes, absolutely.  see attached.
Book4.xlsx
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Saurabh Singh TeotiaCommented:
You can use the following code to do what you are looking for..

Sub copydata()
    Dim ws As Worksheet, ws1 As Worksheet
    Dim rng As Range, r As Range
    Set ws = Sheets("Avg Daily Vol")
    Set ws1 = Sheets("VS")

    Set rng = ws1.Range("B8:K8,B11:K11,B14:K14,B17")
    MsgBox Day(ws.Range("a5").Value)

    Set r = rng.Find(What:=Day(ws.Range("a5").Value), After:=ws1.Range("B8"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    r.Offset(1, 0).Value = ws.Range("b5").Value

End Sub

Open in new window


Saurabh...
0
 
jfrank85Author Commented:
thanks!

i tried to modify it, as i run the macro from a different workbook.

i tested and it's finding the workbook correctly, however under 'test copy/paste for data i'm getting stuck. i get an error on set ws= Sheets("Avg Daily Vol").
run-time error 9. subscript out of range.

Sub copydata()
Dim strSourcePathname As String, Report As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    Dim wbForCopy As Workbook

    Set Report = ActiveWorkbook

    strSourcePathname = Report.Worksheets(1).Range("A98").Text & ".xlsx"
    strSourcePathname = Trim(strSourcePathname)
    MsgBox "Good, " & strSourcePathname & " opened."
    Set wbForCopy = IsOpen(strSourcePathname)
    If Not wbForCopy Is Nothing Then
    
    'test copy/paste for data
    Set ws = Sheets("Avg Daily Vol")
    Set ws1 = Sheets("VS")
  
    Set rng = ws1.Range("B8:K8,B11:K11,B14:K14,B17")
    MsgBox Day(ws.Range("a5").Value)

    Set r = rng.Find(What:=Day(ws.Range("a5").Value), After:=ws1.Range("B8"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    r.Offset(1, 0).Value = ws.Range("b5").Value
 Else
        MsgBox "Today's report is currently not opened."
    End If
End Sub

Open in new window

0
 
Saurabh Singh TeotiaCommented:
I don't see you declaring ws anywhere in the code.. declare the same as worksheet by...

dim ws as worksheet

Open in new window

0
 
jfrank85Author Commented:
still subscript out of range.

Set ws = Sheets("Avg Daily Vol")
    Set ws1 = Sheets("VS")

these sheets are on wbForCopy.

Should i have wbForCopy.Sheets("Avg Daily Vol") ?

I tried that and still get subscript out of range.
0
 
Saurabh Singh TeotiaCommented:
Yes that should take care of it but i don't see in your code where you are opening that workbook
0
 
jfrank85Author Commented:
it's already opened manually. my code detects the open workbook just fine...

it's then getting stuck.


Sub copydata()
    Dim strSourcePathname As String, Report As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    Dim wbForCopy As Workbook
    Dim ws As Worksheet
    
    Set Report = ActiveWorkbook

    strSourcePathname = Report.Worksheets(1).Range("A98").Text & ".xlsx"
    strSourcePathname = Trim(strSourcePathname)
    MsgBox "Good, " & strSourcePathname & " opened."
    Set wbForCopy = IsOpen(strSourcePathname)
    If Not wbForCopy Is Nothing Then
    
    'test copy/paste for data
    Set ws = wbForCopy.Sheets("Avg Daily Vol")
    Set ws1 = wbForCopy.Sheets("VS")
  
    Set rng = ws1.Range("B8:K8,B11:K11,B14:K14,B17")
    MsgBox Day(ws.Range("a5").Value)

    Set r = rng.Find(What:=Day(ws.Range("a5").Value), After:=ws1.Range("B8"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    r.Offset(1, 0).Value = ws.Range("b5").Value
 Else
        MsgBox "Today's report is currently not opened."
    End If
End Sub

Open in new window

0
 
Saurabh Singh TeotiaCommented:
Can you post me the function of isopen .. so that i can look into it and make necessary changes accordingly..
0
 
jfrank85Author Commented:
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

0
 
Saurabh Singh TeotiaCommented:
Can you stop the code at
    Set wbForCopy = IsOpen(strSourcePathname)

and see does wbforcopy has any value in it or it's not blank??

Then again help me understand the sheet name you are writing and the sheet name in the code is exactly the same, their is no difference of spaces or anything else..
0
 
jfrank85Author Commented:
sure, the open workbook's name is on the macro workbook's Range("A98").Text & ".xlsx"

strSourcePathname = "March 18 2015.xlsx", so that's the workbook i want to do all the work for copy/paste.

wbForCopy = Nothing
0
 
jfrank85Author Commented:
perfect, thanks for that!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.