Automation in Excel

[karlp]

I want to automate some repetitive tasks in excel which seem quite easy but are time consuming.

We gather data from a scientific instrument , check it and then produce a .csv file (it can be an excel file if needed).

This will contain multiple columns of data that are approx 5o rows.

It is all on one sheet but contains multiple sets of individual data sets consisting of 3 columns of data repeated (this repetition can vary from day to day)

I want to copy these 3 rows of data into a separate excel template and then repeat the process for the remaining columns into separate excel files.


If I could preselect the cell range and then enter the number of reiterations that would probably do what I require.

Any help would be appreciated

 

[leadfarmer]

The easiest way would probably be to record a macro whilst performing the actions you need to repeat

You can edit the code of the macro afterwards if needed but this way you don’t need to know how to code VB in the first place

 

[karlp]

The easiest way would probably be to record a macro whilst performing the actions you need to repeat

You can edit the code of the macro afterwards if needed but this way you don’t need to know how to code VB in the first place

Thanks -yes I was thinking of a macro but the bit I am thinking about is how to do the re iterations.

I.e. select 4 columns , copy, paste

Then move across to the next set of columns - it may be 5 sets or 10 sets or maybe even 30 sets

 

[SBD]

I’d firstly back up the raw data file.

Then copy - paste the columns you require, and then delete them from the working file.

And then repeat.

This will avoid issues with trying to update cell/column references as you go, you always copy eg A1:C50 and then always delete columns A-C.

 

[leadfarmer]

Thanks -yes I was thinking of a macro but the bit I am thinking about is how to do the re iterations.

I.e. select 4 columns , copy, paste

Then move across to the next set of columns - it may be 5 sets or 10 sets or maybe even 30 sets

Record the macro selecting the first range and pasting it to the destination

Then edit the macro, duplicate the lines for the copy and paste but edit the ranges as necessary

Add this paragraph as many times as necessary up to the maximum iterations needed, if there is no data the extra paragraphs will just copy and paste empty cells

 

[karlp]

Record the macro selecting the first range and pasting it to the destination

Then edit the macro, duplicate the lines for the copy and paste but edit the ranges as necessary

Add this paragraph as many times as necessary up to the maximum iterations needed, if there is no data the extra paragraphs will just copy and paste empty cells

Thanks - I will give it a go

 

[karlp]

I have done a simple macro but stuck on this line

ActiveWorkbook.SaveAs Filename:="C:\Users\KP\Desktop\120625_blk.xlsx", _

I want it to look at Cell C1 and take the info from there and save as the file name

 

[leadfarmer]

You need to get the cell value and assign it to a variable, then put the variable in the file name line

Have a google about getting cell value or look on YouTube, the syntax depends on your version of VB or VBA

Example here….

VBA セルの値の取得、設定、変更

このチュートリアルでは、VBAを使用してセルの値を操作する方法について説明します。 セルの値を設定する

www.automateexcel.com

 

[karlp]

You need to get the cell value and assign it to a variable, then put the variable in the file name line

Have a google about getting cell value or look on YouTube, the syntax depends on your version of VB or VBA

Example here….

VBA セルの値の取得、設定、変更

このチュートリアルでは、VBAを使用してセルの値を操作する方法について説明します。 セルの値を設定する

www.automateexcel.com

thanks for your help

i will have to look at this and have a good read

thanks again

 

[karlp]

Got it sorted now. - Thanks for the input

 

[SBD]

You need to get the cell value and assign it to a variable, then put the variable in the file name line

Have a google about getting cell value or look on YouTube, the syntax depends on your version of VB or VBA

Example here….

VBA セルの値の取得、設定、変更

このチュートリアルでは、VBAを使用してセルの値を操作する方法について説明します。 セルの値を設定する

www.automateexcel.com

That’s why I suggested deleting the (rows or) columns when you’ve copied them. Then it’s just repeating itself.

Like me I suppose…

 

[karlp]

Couls anyone advise on this please

I am gettin g asyntax error on the last line

essentially i am taking a cell -D1 from one workbook and opening another excel sheet with the name contained in that cell.
then going back to the workbook copying a range of columns and pasting into the workbook opened by the contents of D1

I then want to activate the already opened workbook and copy thecells
It comes up with a syntax error and I cannot work out why


.Open Filename:="C:\Results\PFAS Import Template.xlsm"
Range("D1").Select
Openname = ActiveSheet.Range("d1").Text
Selection.Copy
Workbooks.Open Filename:="C:\Results\" & Openname & ".xlsx"
Windows("PFAS Import Template.xlsm").Activate
Columns("D:G").Select
Selection.Copy
Windows(" & Openname &" .xlsx).Activate

 

[karlp]

I think I have sorted

Windows(Openname &" .xlsx").Activate