Related:
- Macro to copy data from one workbook to another based on criteria
- How would macros be copied from one workbook to another - Best answers
- Copy a row from one worksheet to another, based on criteria ✓ - Forum - Excel
- Macro to copy data to new workbooks based on condition - Forum - Excel
- Macro to copy data from one sheet to another based on criteria ✓ - Forum - Excel
- How to copy data from one sheet to multiple sheets using IF - Forum - Excel
- Copy data from one sheet to another based on condition using VBA - Forum - Excel
3 replies
rizvisa1
Apr 23, 2010 at 11:22 AM
- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022
Apr 23, 2010 at 11:22 AM
Try this. Make sure that you read the NOTE in the code.
Sub CopyData() Dim sBook_t As String Dim sBook_s As String Dim sSheet_t As String Dim sSheet_s As String Dim lMaxRows_t As Long Dim lMaxRows_s As Long Dim sMaxCol_s As String Dim sRange_t As String Dim sRange_s As String sBook_t = "Target Data WB- Copy data to WB.xls" sBook_s = "Source Data WB - Copy data to WB.xls" sSheet_t = "Target WB" sSheet_s = "Source" lMaxRows_t = Workbooks(sBook_t).Sheets(sSheet_t).Cells(Rows.Count, "A").End(xlUp).Row lMaxRows_s = Workbooks(sBook_s).Sheets(sSheet_s).Cells(Rows.Count, "A").End(xlUp).Row sMaxCol_s = Workbooks(sBook_s).Sheets(sSheet_s).Cells(1, Columns.Count).End(xlToLeft).Address sMaxCol_s = Mid(sMaxCol_s, 2, InStr(2, sMaxCol_s, "$") - 2) If (lMaxRows_t = 1) Then sRange_t = "A1:" & sMaxCol_s & lMaxRows_s sRange_s = "A1:" & sMaxCol_s & lMaxRows_s Workbooks(sBook_t).Sheets(sSheet_t).Range(sRange_t) = Workbooks(sBook_s).Sheets(sSheet_s).Range(sRange_s).Value Else sRange_t = "A" & (lMaxRows_t + 1) & ":" & sMaxCol_s & (lMaxRows_t + lMaxRows_s - 1) sRange_s = "A2:" & sMaxCol_s & lMaxRows_s Workbooks(sBook_t).Sheets(sSheet_t).Range(sRange_t) = Workbooks(sBook_s).Sheets(sSheet_s).Range(sRange_s).Value ' ###################### NOTE ################# 'the following lines are to be used of serial number is to be fixed too, instead of being copied ' if there is no need, then delete the line below Workbooks(sBook_t).Sheets(sSheet_t).Range("A" & lMaxRows_t).AutoFill Destination:=Workbooks(sBook_t).Sheets(sSheet_t).Range("A" & lMaxRows_t & ":A" & (lMaxRows_t + lMaxRows_s - 1)), Type:=xlFillSeries End If End Sub
rizvisa1
Apr 22, 2010 at 12:50 PM
- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022
Apr 22, 2010 at 12:50 PM
Could you please upload a sample file on some shared site like https://authentification.site and post back here the link to allow better understanding of how it is now and how you foresee. Have few rows of data
Source WB where I will have the data and macro. Once I update the data here and run the macro, it should get appended from the last row in Target WB. The content must be copied from the 2nd column onwards.
https://authentification.site/files/22081242/Source_Data_WB_-_Copy_data_to_WB.xls
Target WB
https://authentification.site/files/22081254/Target_Data_WB-_Copy_data_to_WB.xls
https://authentification.site/files/22081242/Source_Data_WB_-_Copy_data_to_WB.xls
Target WB
https://authentification.site/files/22081254/Target_Data_WB-_Copy_data_to_WB.xls
Apr 28, 2010 at 01:32 AM
But my need is that the data from the source should get appended from the last row of the target. For this, I guess, we hav to find the blank cell in column two of target and paste the data from source there on.
Apr 28, 2010 at 11:35 AM
https://authentification.site/files/22172909/Target_Data_WB-_Copy_data_to_WB.zip
Try again and if still does not work, upload files with macro and data to some share site and post back the link
Apr 29, 2010 at 02:07 AM
Uploaded the same files in the above link with a word file having the snapshots of the error.
Can we have the source and target file in different locations? The assigning of the strings need to be given the paths I believe. Please confirm on this.
Apr 29, 2010 at 02:27 AM
Yes the files can be where ever you want. Only requirements are that both book be opened via same excel process. By same process I mean, that you start excel and then open one book. Then you again launch excel and open the other book. This cause two processed. Just launch excel and open both books via same excel process.
Apr 29, 2010 at 11:58 PM
But my requirement is that I wont be opening the target file. The target file stays on a server and takes much longer time to open. Thats why I am trying for a macro to avoid opening the target file. I will have a temporary file on my desktop which I wil be using as source.