Dynamic calculation sheet copying colums with variable length

[Ealglez]

Dear MrExcel.com,

I am completely new in the malarkey of writing macros in excel so I am a bit lost in my new task.
I would like to create a calculation file which implies the following:
a) creates different worksheets (w/s) for each case.
b) copy data from the data dump worksheet to each case worksheet.
I would like to start from b) as a) seems much more difficult to me, so let's assume I have only one case: Case A.

I have downloaded time vs. variable "AAA" data in w/s "Data" in the range A6:B654.
I would like to copy these data in w/s "Case A" starting in row 6 (first data point in A6:B6).

I would like to create a macro (from scratch, please!) so that I can copy all the data from case A in w/s "Case A" but that allows me to do exactly the same when the data is of a different length, i.e. if I have a Case A in w/s "Data" which is in the range A6:B800 instead of A6:B654 (so in w/w "Case A") I will have cells A6:B800 populated.

I have tried to find an answer in the Forums, but as I am completely new on this, I am a bit confused as they look as parts of the macro instead of the whole macro it self. I have tried to record de macro and modified it afterwards but it is not working either.

Any help will be much appreciated.
Thanks very much,
Ealglz.

 

[SteveO59L]

Sheets("Data").Select
Range("A6:b" & lastrow).Copy
Sheets("Case A").Select
Range("a6").Select
ActiveSheet.Paste

 

[Ealglez]

Thanks for the reply, SteveO59L.

Have tried to use the coding above but it keeps given me an error on row 2. I think the system does understand when in a range the second boundary has not a number ("A6:B"(. Is there any other way around I can try?

Many thanks,
Ealglez

 

[SteveO59L]

apologies, I forgot to define the variable lastrow

Dim lastrow As Long
Sheets("Data").Select
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
Range("A6:b" & lastrow).Copy
Sheets("Case A").Select
Range("a6").Select
ActiveSheet.Paste

 

[Ealglez]

Hi SteveO59L,

It worked!!! Thank you very much!

Do you think it is possible to do the following using macros?:
a) I have a list of cases in w/s "Summary" fro A4 (the length can varied as well). Say I have 4 cases as an example.
b) I can create as many different w/s as in the list so w/s from Case A to Case D in the example.
c) For each of the w/s I want to do the same as I did for Case A (copy data from "Data" w/s) but the set of data is in different location on "Data" w/s: Case A data in A6:B100. Case B in C6:D100. Case C in E6:F100. Case D in G6:H100. Again the length of the columns is variable. Can a macro do all this automatically taking into account that the number of cases may vary affecting both w/s and copy and paste options?
d) In each worksheet there are other calculation based in that time vs. AAA variable set of data. For example a MAX calculation. Is there a way to set this formula so that it adapts to the different length the set of data will have for each particular case?
I understand that this may be far to complicated though!
Thank a lot in advance!
Ealglez