CODEWITHSUNDEEP
excelvba
Cassiopeia
Cassiopeia

Reputation: 313

Batch pasting data into excel

I have a load of data which I need to process. I process it by pasting the raw data into a pre-made excel spreadsheet, trimming a few rows of zeros at the bottom of the processing (not pasted in) columns, and then running a macro to remove cells with 0 in them in column P.

Each spreadsheet takes 6 sets of raw data, each in a separate worksheet. The zero-removing macro takes a while so I would like to do the pasting as a separate job if I can.

I feel like this should be pretty simple but unfortunately I only have a very basic knowledge of code. My idea so far has been to try and use AutoHotKey to record a sequence to do it, but unfortunately it is clunky and hasn't worked so far because alt-tabbing is unreliable or some other issue.

I would like to make a code which switches between windows, copying the data from one excel (tab delimited .txt) file into the main .xlsm document.

I would manually save it and then open the next 6 datasets and a blank processing workbook.

Thanks for your time x

Edit: The datasets are always called a,a1,a2,b,b1,b2. They are always located in the same folder as the data processing spreadsheet. The data processing spreadsheet is always called [processor.xlsm]

Edit2: This is where I am:

Sub ImportData1()

'Prevent windows asking about saving clipboard data
Application.DisplayAlerts = False

'select dataset a
Worksheets("SeqA Run1").Activate
Workbooks.Open Filename:=ThisWorkbook.Path & "\a.txt"
ActiveSheet.Range("A4:I1000").Copy

Windows("Surge Test Data Analysis Importer.xlsm").Activate
Range("A7").Select
ActiveSheet.Paste
''Close dataset
Windows("a.txt").Activate
ActiveWorkbook.Close


'Select dataset a1
Worksheets("SeqA Run2").Activate
Workbooks.Open Filename:=ThisWorkbook.Path & "\a1.txt"
ActiveSheet.Range("A4:I1000").Copy

Windows("Surge Test Data Analysis Importer.xlsm").Activate
Range("A7").Select
ActiveSheet.Paste
''Close dataset
Windows("a1.txt").Activate
ActiveWorkbook.Close

'(repeat several times)


'Re-enable windows prompts about clipboards etc

Application.DisplayAlerts = True


End Sub

Upvotes: 0

Views: 2001

Answers (1)

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15561

You do not need to switch between windows. You have to get references to the worksheets, and use the references. The first thing to do is record a macro, and then do whatever you would do, manually. Stop recording, and then start modifying the resulting macro.

Then, in your macro,

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set wb1 = Workbooks("<The name of your target Workbook>")
Set wb2 = Workbooks("<The name of your source Workbook>")
Set ws1 = wb1.Worksheets("<The name of your target> Worksheet")
Set ws2 = wb2.Worksheets("<The name of your source> Worksheet")
ws2.UsedRange.Copy
ws1.Paste

You may need to paste at a specific location in your Target.

Repeating requires looping (e.g., For ... Next). If you provide more info on your case, it would be helpful.

Remember that assigning to a variable of type Worksheet should be preceded by Set.

Upvotes: 1

Related Questions