need Macro to copy from one workbook to another

[Revathi]

Hi all,



Example : I need to copy the data from the workbook 1 from the sheet data and paste it in the workbook 2 to the sheet raw wherein the sheet "raw" already have some data but i need to paste these new data to the below on those old data.

Please assist on this.

 

[Yongle]

is workbook1 already open?
is workbook2 already open?
in workbook1 are headers in row 1 and does data to be copied start in row 2?
in workbook1 does column A have data in every row? (need to find last used row - can column A be used?)

 

[Yongle]

try this ...

assumes
- both workbook are open
- headers are in row1\ data starts in A2
- last entry in column A marks last row

amend
- last column (currently A to T are copied)
- workbook names & extensions to match actual names

Sub CopyData()
    Const LastColumn = "[COLOR=#ff0000]T[/COLOR]"
    Dim sh As Worksheet, cel As Range, rng As Range
    Set sh = Workbooks("[COLOR=#ff0000]wb1Name.xlsx[/COLOR]").Sheets("data")
    Set cel = Workbooks("[COLOR=#ff0000]wb2Name.xlsx[/COLOR]").Sheets("raw").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Set rng = sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp)).Resize(, Range(LastColumn & 1).Column)
    rng.Copy cel
End Sub

 

[Revathi]

HI Yongle,

I am getting a bug called subscript out of range. Below is my requirement with clear points.

> Both the workbooks are already opened
> I need to copy the data which is available in workbook 2 in yyy sheet without headers and i need to paste the same in workbook 1 in xxx sheet at A348980 cell.. however every week this range will change.
> Please help how do i need to paste the data at the bottom of the details available in Workbook 1.

 

[Revathi]

Hi Yongle,

I have identified the bug and now its works perfectly.

Thanks.

 

[Yongle]

thanks for the feedback