Copy from one location and split to two locations

[MichaelATO]

Hi
I have attached a work in progress file that mimics very closely the work file. The file "DataAnalysis.zip" is attached.

What I require is for staff to use the "first_dataset" sheet and to import a clients details from the imported data page.
The imported data sheet has all the details required but the details must be split into two sheets.
For both scenarios the details are copied to Range("A20") and populate down.
First scenario -
First dataset: to contain all clients details (columns A:F but only cost columns from 1 to 5 and
Second dataset: to contain all clients details (columns A:F) but only cost columns from 1 to 6.
I have already incorporated a vba macro to do the copy to both sheets but all columns are copied.
The second scenario:
Once the data has been copied to the sheets how can I get the formulas, which are all different (Shown in "Second_Dataset" row 21, column L to O), in Second Dataset starting in column L:O to copy down until the end of any value shown in column A?
Any assistance is greatly appreciated. Thank You
Regards
Michael

[rbrhodes]

Hi,

Here's a reworked version of your example. I took a few liberties with copying, etc. but it should be simple for you to change the code.

Post back if it helped at all or with any questions.

Cheers,

dr

[MichaelATO]

Thanks DR.
It does the trick very well. Thanks muchly.
I do ask for one more favour as this is the part I am having difficulty with:
How can I (as you have done) for WS2 copy only columns 1 to 11 but for WS3 copy only columns 1 to 6 and 12 to 16?
I have tried expanding the initial range criteria by breaking it into a number of subsets but this does not work?



'Get row number of last used row
r = Range("A65536").End(xlUp).Row
'Select A1 to Col 'P', last used row
Range(Cells(1, 1), Cells(r, "A:F,G:K,L:P")).Select
'Select and sort as before
Selection.Name = "Database"
Range("Database").AutoFilter Field:=1, Criteria1:=Str1, Operator:=xlAnd
Range("Database").AutoFilter Field:=2, Criteria1:=Str2
Range("A1").Select
'Copy delimited range to First_Dataset
Range(Cells(1, 1), Cells(r, 11)).Copy Destination:=WS2.Range("A20")
'Copy delimited range to Second_Dataset
Range(Cells(1, 1), Cells(r, "A:F,L:P")).Copy Destination:=WS3.Range("A20")


Thanks
Michael

[rbrhodes]

Hi,

Although 'Cells' gives the range object excellent room to work with VARIABLES it doesn't (as far as I know) work with multiple ranges. So your question becomes a two part copy as follows:

Simply substitute this for the one line 'Copy to Second_Dataset'




'Copy 1ST PART of delimited range to Second_Dataset: A20 as destination
Range(Cells(1, 1), Cells(r, 6)).Copy Destination:=WS3.Range("A20"

'Copy 2ND PART of delimited range to Second_Dataset at G20 as destination (add to existing row data)
Range(Cells(1, 12), Cells(r, 16)).Copy Destination:=WS3.Range("G20")


Cheers,

dr

[MichaelATO]

Thanks DR
that's done the trick.
Wanna know something funny?
I had previous code hidden on my harddrive, burried under mounds of text files that had the same answer as this second reply.
Oh, if only I could remember where I put things...........sigh!!!!!!!!

thanks again
Cheers
Michael