• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Code for pasting data in last row

V

Veeru106

Member
Hi,


I am trying to open my documents and select a workbook from documents.

Then I will copy certain range from that workbook and paste in my master sheet

I have some data in my master sheet and I want to paste below it

Code I have working fine except I am not able to find last row and paste in it


Please suggest…

Thanks
 
Code: 
Sub Copy()
Call first
Dim vFile As Variant
Dim wbCopyTo As Workbook
Dim wsCopyTo As Worksheet
Dim wbCopyFrom As Workbook
Dim wsCopyFrom As Worksheet

Set wbCopyTo = ActiveWorkbook
Set wsCopyTo = ActiveSheet

    '-------------------------------------------------------------
    'Open file with data to be copied
   
    vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & _
    "*.xl*", 1, "Select Excel File", "Open", False)
   
    'If Cancel then Exit
    If TypeName(vFile) = "Boolean" Then
        Exit Sub
    Else
    Set wbCopyFrom = Workbooks.Open(vFile)
    Set wsCopyFrom = wbCopyFrom.Worksheets(1)
    End If
   
    '--------------------------------------------------------------
    'Copy Range
    wsCopyFrom.Range("B2:D70").Copy
    wsCopyTo.Range("A1").PasteSpecial Paste:=xlPasteValues, _
            Operation:=xlNone, SkipBlanks:=False, Transpose:=False
   
    'Close file that was opened
    wbCopyFrom.Close SaveChanges:=False

End Sub
 
Hi,


Yes I totally adhere to rules and respect them…I am in this group for a month now…This is an awesome initiative to get learn and get solutions to various issues or problems.


I sincerely apologies if I have broken any rule unknowingly.


I know I am raising lot of issues and creating lot threads but I think this is the soul of this forum to discuss issues and achieve excellence and this will also help others to learn from various codes we posted.


They can relate to my issues and other issues and learn from it.


Please let me know your thoughts or help me if I need to improve .


Thanks
 
And with my R&D I came with
Code: 
'Copy Range
    wsCopyFrom.Range("A1:D10").Copy
   
  Sheets("Geet").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, _
  Operation:=xlNone, SkipBlanks:=False, Transpose:=False
   
    'Close file that was opened
    wbCopyFrom.Close SaveChanges:=False

End Sub

but it says Subscript out of Range.
 
Hi, Veeru106!

The posted snippet is not the whole subroutine code, but despite of that try this:
Code: 
'Copy Range
   wsCopyFrom.Range("A1:D10").Copy
   
  with Sheets("Geet")
    .Range("A" & .Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  end with 
   
    'Close file that was opened
   wbCopyFrom.Close SaveChanges:=False

End Sub
Regards!
 
Thanks for replying..ok now if I add your code then it gives me syntax error
Please have a look or suggest something better.
Code: 
Sub Copy()
Dim vFile As Variant
Dim wbCopyTo As Workbook
Dim wsCopyTo As Worksheet
Dim wbCopyFrom As Workbook
Dim wsCopyFrom As Worksheet

Set wbCopyTo = ActiveWorkbook
Set wsCopyTo = ActiveSheet

    '-------------------------------------------------------------
    'Open file with data to be copied
   
    vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & _
    "*.xl*", 1, "Select Excel File", "Open", False)
   
    'If Cancel then Exit
    If TypeName(vFile) = "Boolean" Then
        Exit Sub
    Else
    Set wbCopyFrom = Workbooks.Open(vFile)
    Set wsCopyFrom = wbCopyFrom.Worksheets(1)
    End If
   
    '--------------------------------------------------------------
    'Copy Range
  wsCopyFrom.Range("A1:D10").Copy
   
  With Sheets("Geet")
  .Range("A" & .Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues,
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False
   
  End With
   
    'Close file that was opened
  wbCopyFrom.Close SaveChanges:=False

End Sub
 
Thanks again but now error changes to Subscription out of range.


I also attaching 2 worksheets…from where (Geet) I want to get data and 2nd one (Var)to where I want to paste in


Please have a look and suggest if anything is wrong
 

Attachments

  • Geet.xlsx
    8.2 KB · Views: 3
  • Var.xlsm
    20.9 KB · Views: 5
Hi, Veeru106!

Honestly, I forgot about this thread.
Now with the whole code... you have to:

a) change this line:
Code: 
With Sheets("Geet")
by this one:
Code: 
With wsCopyTo
so as to use the yet defined names and easy coding

b) insert this line:
Code: 
wbCopyTo.Activate
before the line referred in a)

Regards!
 
Hi, Veeru106!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!
 
SirJB7...sorry for coming back on this...code is fine..but this will work if my sheet has only one sheet ...now in new scenario I have multiple sheet in workbook and I want data from that particular sheet....
I am adding below code
Code: 
Sheets("MTD").Select
in between
Code: 
  wsCopyFrom.Range("A2:D10").Copy
So that my combine code will be
Code: 
  wsCopyFrom.Sheets("MTD").Select.Range("A2:D10").Copy
But it is giving me compile error "Method or data not found"...
 
Hi, Veeru106!
I won't be able to check codes until next week, but there's an error yet.
If I don't remember wrongly, wsCopyFrom was a worksheet object, so
Code: 
wsCopyFrom.Range("A2:D10").Copy
should work as you're referring to a range in a worksheet (worksheet.range)..
But
Code: 
wsCopyFrom.Sheets("MTD").Select.Range("A2:D10").Copy
shouldn't as ".Sheets("MTD")" isn't a valid qualifier of another worksheet.
Resolve that using logic. Maybe redefining wsCopyFrom/To instructions.
Regards!
 
Last edited:
ok I tried it....but not able to define it.......changing wsCopyfrom TO wsCopyto will not solve it I guess because,wscopyfrom define from where we want to import data and wscopyto define to where we want to export data......what really need is workbook from where we importing data has 3 tabs and I want data from one particular tab (example attached) I want data from MTD tab,,not from Nam tab....hence trying to add MTD tab name in between...Please take a lookwhenever you are back...Thanks as always...
 

Attachments

  • Geet.xlsx
    8.8 KB · Views: 3
Hi, Veeru106!
Change this:
Code: 
    Set wsCopyFrom = wbCopyFrom.Worksheets(1)
by this:
Code: 
    Set wsCopyFrom = wbCopyFrom.Worksheets("MTD")
Regards!
 
Hi, Veeru106!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!
 
You must log in or register to reply here.
Back
Top