• 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.

Copy and paste sections of one worksheet to another depending on criteria

S

spk1009

New Member
I am working with 2 workbooks; one is the template (Tab1 Form, Tab2 data) and the 2nd one is the daily source file. On a daily basis, I need to copy over from the 2nd file to template tab2 worksheet ONLY the section of information that has the corresponding file stock#.


http://speedy.sh/KwSQ4/Template.xlsx

http://speedy.sh/zptjZ/DAILY-SOURCE-FILE.xls
 
Hi ,


Do you mean to say the following rows of data are to be copied ?

[pre]
Code: 
26	409	NAME1	509156			1.00
26	2190	NAME2	509157			2.00

26	26	NAME1	509158			5
[/pre]

If this is so , these two items of data have different account numbers ; are these not required ?


Narayan
 
Yes, so let's say the template I'm working on is for stock#26, I need to pull over all the (account) data where Stock is 26.


thank you...
 
Hi ,


Does that mean the tab1 ( Form ) has to be checked first ( since that is the place where the stock number is available ) ?


Suppose the stock number on the Form is different from the stock numbers in the daily source file , will no data be transferred to tab2 ?


Narayan
 
Yes..thats correct. Some stock#'s will not be there every day...then the form will remain blank...thanx!
 
Hi ,


Thanks for the clarifications. I hope someone else can help you out if it is urgent ; it is late for me , and I can give you help only tomorrow morning.


Narayan
 
Hi ,


Check out the workbook at the following link :


https://docs.google.com/open?id=0B0KMpuzr3MTVZnQ0NG5QUGZRT2VVVk1LMm5DQkJXZw


Please note that the macro is to be run after opening the DAILY-SOURCE-FILE.xlsm workbook also.


Narayan
 
Hi, spk1009!


Its name is "Copy_From_Daily_Source_File" (unquoted) and you can find it under the "ThisWorkbook" section.

It gets directly displayed when you enter the VBA editor (Alt-F11 from Excel).


Regards!
 
Thank you, SirJB7...the macro works perfect...but there is a section of the "DAILY SOURCE FILE" that I also need to bring into the Workbook "TEMPLATE", tab 'data'.


I need to pull in the 'ACCT 126' , 'ACCT127....etc for each section of the stock number.


thank you
 
Hi, spk1009!


I only dowloaded NARAYANK991's file just for checking the name for you. As haven't followed the whole thread I prefer that the author answer that.


Regards!


PS: BTW, that workbooks sound familiar to me... are the same of those for which I wrote you a couple of macros weeks go, aren't?
 
Hi, spk1009!


I'm sure NARAYANK991 will dissipate all your doubts. Just advise if any modification is required to my original suggested code. See you tomorrow!


Regards!
 
@NARAYANK991

Hi!

I just took the post for a while, all your's again. Please advise if I can help.

Regards!
 
Hi fellow members...thank you for all your efforts...I truly appreciate all your help!


The VB script looks great....but I'm stumped on the 'subscript out of range' error i'm getting when I run the macro...??? any ideas..?? thanx again!
 
So I have this script....with the help of forum...


Public Sub Copy_From_Daily_Source_File()

' I have assumed that if the program comes across 15 blank rows ,

' it means there is no more data to be processed ; I have used 15 as the limit

' because in the existing sheet , the maximum number of blank rows is 10.

Const MAX_BLANK_ROWS = 15

Const COLUMNS_TO_BE_COPIED = 7

Const ACCOUNT_TITLE = "ACCT:"


Workbooks("Template.xlsm").Worksheets("data").Activate

Set origin = Workbooks("Template.xlsm").Worksheets("data").Range("A5")

Workbooks("DAILY-SOURCE-FILE.xlsm").Worksheets("ckreg").Activate

Set sfn = Workbooks("DAILY-SOURCE-FILE.xlsm").Worksheets("ckreg").Range("A1")


curr_stock_num = ThisWorkbook.Worksheets("FORM").Range("Stockcell")

If curr_stock_num > 0 Then

Application.ScreenUpdating = False

num_of_blank_rows = 0

row_counter = 0

Do

' Select the source file

Workbooks("DAILY-SOURCE-FILE.xlsm").Activate

sfn.Select

ccv = ActiveCell.Offset(row_counter, 0).Value

' Stock number matches

If ccv = curr_stock_num Then

' We need to copy the stock data from the daily source file

' to the sheet labelled "data" in the Template.xlsm file

ActiveCell.Offset(row_counter, 0).Resize(1, COLUMNS_TO_BE_COPIED).Copy

Workbooks("Template.xlsm").Activate

origin.Select

' We need to check the last row of data already present in the sheet

' and go the next row , which will be the first blank row

' available for pasting new data.

' If the first column is blank , it is assumed the entire row is blank.

If ActiveCell = "" Then

ActiveCell = saved_acct

ActiveCell.Offset(0, 1).PasteSpecial xlPasteValuesAndNumberFormats

Else

If ActiveCell.Offset(1, 0) = "" Then

ActiveCell.Offset(1, 0) = saved_acct

ActiveCell.Offset(1, 1).PasteSpecial xlPasteValuesAndNumberFormats

Else

ActiveCell.End(xlDown).Offset(1, 0) = saved_acct

ActiveCell.End(xlDown).Offset(0, 1).PasteSpecial xlPasteValuesAndNumberFormats

End If

End If

Else

' Stock number does not match ; check whether the row is blank or not

If ccv = "" Then

num_of_blank_rows = num_of_blank_rows + 1

Else

num_of_blank_rows = 0

If Left(Trim(ccv), 5) = ACCOUNT_TITLE Then

saved_acct = ccv

End If

End If

End If

row_counter = row_counter + 1

Loop Until num_of_blank_rows >= MAX_BLANK_ROWS

Application.CutCopyMode = False

Application.ScreenUpdating = True

End If

Set origin = Nothing

Set sfn = Nothing

End Sub


But I keep getting a subscript out of range" error when I run the macro....


any ideas?? Please help


thank you all
 
Hi ,


This is the macro I had sent you.


I had the Template.xlsm and DAILY-SOURCE-FILE.xlsm workbooks with me ; I copied the macro which you have posted , into the ThisWorkBook section of the VBA Project in the Template.xlsm file , entered 26 as the Stock number in the FORM tab , and ran the macro.


It completed without any errors and the result was that the rows containing the stock number 26 in the DAILY-SOURCE-FILE.xlsm workbook were copied to the "data" tab in the Template.xlsm workbook.


Have you done this ?


Narayan
 
yes...its the macro you sent...its fabulous..I guess I'm not doing something right..I will try again. Thank you soooo much!


Shetal
 
I know what I did wrong....I didn't post it in " ThisWorkBook section of the VBA Project in the Template.xlsm file ".
 
Thank you all in advance.


So with these same files, how would I change the code if I had two tabs (FORM1) and (FORM2) with separate stock# on the Template.xlsm file?


Thank you.
 
You must log in or register to reply here.
Back
Top