copy and paste from file

K

kaffal

Board Regular
Joined
Mar 7, 2009
Messages
68
I would like to have a macro to copy and paste from another workbook.

Target Workbook,"Main"
<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 64px;"><col style="width: 143px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td> </td><td>A</td><td>B</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>Folder:</td><td>C;\data\sales</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td>Filename :</td><td>150309_salesrecord.xls</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td>worksheet</td><td>marSales</td></tr></tbody></table>
I have 2 workbooks to start with. One is Target Workbook, with "Main" and "marSales" worksheet. The other is 150309_salesrecord.xls.

I would like to have a macro, to copy from 150309_salesrecord.xls to current workbook, to sheet name"marSales". The folder is look at, the files name to copied and the worksheet to paste to are all defined in B1 to B3 as user input.

I would only wan to copy from 150309_salesrecord.xls, the following.
- 4th row to the last available row.
- Column B to last available column
- It will be pasted into the marSales workbook, starting for row 2. Column 1(cell A2). No extra column will be copied over as there is some formula found in the worksheet and i do not wan to be overwrite.


 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
You have posted a lot of the needed information to get a quick answer.

I just have the following questions:
1. Will the workbook "150309_salesrecord.xls" be opened or closed when you start your macro?
2. What is the name of the worksheet you want to copy from?
 
Upvote 0
hi ,
1) 150309_salesrecord.xls will be closed and be found in the folder in cell B2.
2) The name of the worksheet to be copied from is random. however the 150309_salesrecord.xls will only contain one sheet, thus i think can use something like activeworksheet?
 
Upvote 0
This does what you are asking in my test workbooks:
Code: 
Sub CopySales()
Dim OrigWB As String, SourceWS As String
Dim lrow As Integer, lcol As Integer
    'Assign variables
    OrigWB = ActiveWorkbook.Name
    SourcePath = Sheets("Main").Range("B1")
    SourceWB = Sheets("Main").Range("B2")
    TargetWS = Sheets("Main").Range("B3")

    'Open WorkBook
    With Workbooks.Open(SourcePath & "\" & SourceWB)
        'Assign variables from Source Workbook
        SourceWS = ActiveSheet.Name
        lrow = Sheets(SourceWS).Range("A65536").End(xlUp).Row
        lcol = Sheets(SourceWS).UsedRange.Columns.Count
        'Copy Source data to Target
        Sheets(SourceWS).Range(Sheets(SourceWS).Cells(4, "B"), (Sheets(SourceWS).Cells(lrow, lcol))).Copy _
        Workbooks(OrigWB).Sheets(TargetWS).Range("A2")
        'Close Workbook
        .Close
    End With
End Sub
Note:
The path on your "Main" worksheet, cell B1 is listed using a SemiColon, it should be a full Colon.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
import specific columns from one workbook to another from matching header with vba
Replies
1
Views
136
Peter_SSs
Peter_SSs
D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
M
  • Question Question
Find 1st instance in column A, paste corresponding value from column B into another cell and continue down until end of data
Replies
2
Views
81
MrsFraser07
M
L
  • Question Question
Searchable data validation list problem
Replies
0
Views
52
Lschuman
L
K
  • Question Question
Copy data from vertical cells paste into horizontal cells that will change each time macro run
Replies
1
Views
136
sxhall
S

Forum statistics

Threads
1,224,599
Messages
6,179,831
Members
452,947
Latest member
Gerry_F

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top