Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

macro excel

ResolvedVersion Standard

Mike has attended:
No courses

Macro in Excel

I have a macro that copies data from one workbook and places it in another one. Until recently it was working fine now however it runs like it is working but never copies any data. The macro is designed to copy only unique data so if the document number is already in the new workbook it will not be copied(another issue for later) I have verified that since row 205 in the new WB the data in the original WB is unique yet the macro will not copy it. Can someone help me trouble shoot this issue or explain why it has stopped working/
Thanks

RE: Macro in Excel

I have pasted the Macro code below that I am using. In the main spreadsheet data is entered manually Column 1 will be blank, have MIPR, 9L or Form36 in it. Column 2 has the document number (IE F3LNGA8067G001) the next few columns are a discription dates entered, the organization sent to, and current status. The Macro is designed to pull the data from each row where column 1 is equal to "MIPR" and place this data in the MIPR spreadsheet.

Sub importer()
Dim wrkbk As Workbook
Dim curbk As Workbook
Set curbk = ActiveWorkbook
Dim filt As String
Dim countervar
Dim offsetvar
offsetvar = WorksheetFunction.CountA(Range("A:A"))
countervar = 0
filt = InputBox("What filter do you want to use", "Filter", "MIPR")
Dim nm

nm = Application.Dialogs(xlDialogOpen).Show
If nm = True Then
Set wrkbk = ActiveWorkbook
nm = wrkbk.ActiveSheet.Range("b1").Value
While Len(nm) > 0
If InStr(wrkbk.ActiveSheet.Range("a1").Offset(countervar, 0).Value, filt) > 0 Then
If WorksheetFunction.CountIf(curbk.ActiveSheet.Range("B:B"), wrkbk.ActiveSheet.Range("b1").Offset(countervar, 0).Value) = 0 Then
curbk.ActiveSheet.Range("1:1").Offset(offsetvar, 0).Value = wrkbk.ActiveSheet.Range("1:1").Offset(countervar, 0).Value
offsetvar = offsetvar + 1
End If
End If
countervar = countervar + 1
nm = wrkbk.ActiveSheet.Range("b1").Offset(countervar, 0).Value
Wend
wrkbk.Close False
End If

End Sub

 

Training courses

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Excel tip:

Transpose text

You can transpose any range of cells, turning the columns into rows and the rows into columns. Just follow these steps:

Select the range.

Click the Copy button on the Standard toolbar to copy it to the Clipboard.

Select a cell outside of the range you copied.

Select Paste Special from the Edit menu.

In the Paste Special dialog box, click Transpose, then OK.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.6 secs.