Page 1 of 1

[Solved] Fill a cell and copy a range to another worksheet

PostPosted: Mon Jul 09, 2018 6:43 am
by sokolowitzky
Dear All,

I need a simple macro, which can not be recorded by a macro recorder because it covers two different workbooks. I've been looking around for a while but I could not find much useful solutions on web.

I have a workbook lets say named FPST, which is given its name in cell A2. I fill cell B2 (only with numbers from 1 to 4 or 1to 10) in its first sheet then I copy the results in the second sheet from AW1:BA2680.
And then I insert (past special) these results as values in another workbook named according to cell A2 and "_01". So I need to name the latter workbook this way [what is written in A1]&"_"&01
I could not find much similar things on web. Is that because this is a very simple question and I'm the only one who can't figure it out, or is this something one can not do by macros?
what I need is a macro which does
1)Type numbers, which are given as code, into Sheet1.B2
2)Create a new file named [what is written in A1]&"_"&01, lets say "FPST_01"
3)Copy from workbook "FPST" Sheet2.AW1:BA2680. to "FPST_01" Sheet1.A1:E2680 .
4)While pasting these copied data, if B2 of first sheet equals to 1, macro should paste data to A1:E2680, BUT, if B2 equals to 2 then it should paste to F1:J2680 if B2 equals to 3 then macro should paste data to K1:O2680.

Is there anyone who has an idea about these 4 questions above?

Re: A Macro to fill a cell and copy a range to another works

PostPosted: Mon Jul 09, 2018 7:04 am
by Zizi64
Sample code for the Step 1):

Code: Select all   Expand viewCollapse view
Sub mymacro1

Dim oDoc1 as object
' you need more Document object(s) for the next steps

Dim oSheet1_1 as object
' you need more Sheet object(s) for the next steps

Dim oCell1_1_1 as object
' you need more Cell object(s) for the next steps


rem Step 1: fill a cell with a numeric constant
   oDoc1 = ThisComponent
   oSheet1_1 = oDoc1.Sheets.GetbyName("Sheet1")
   oCell1_1_1 = oSheet1_1.getCellByPosition(0, 1)
   oCell1_1_1.SetValue(1234)
rem   oCell1_1_1.SetString("1234") 'for a string cell content


rem Step 2:
Rem ... etc...



End Sub

Re: A Macro to fill a cell and copy a range to another works

PostPosted: Mon Jul 09, 2018 7:21 am
by Zizi64

Re: A Macro to fill a cell and copy a range to another works

PostPosted: Wed Jul 11, 2018 6:32 am
by sokolowitzky
To be honest, getting skilled with basic was not my aim. I need just to do some tasks faster. And I know that the functions I question are very basic.
But your messages shed a light. I think I can find myself.
Thank you for your support. I'm tagging this as solved, even though I did not get what I need.