macro excel

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

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Macro in Excel

Macro in Excel

resolvedResolved · Low Priority · Version 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:

Hide separate columns in Excel 2010

If you want to hide columns not adjacent to each other for example, Columns A, C and E then:-

1) Click on the fist column to be hidden i.e. A

2) Press and hold down the CTRL key

3) While holding the CTRL key, left click on the rest of the columns you want to hide i.e. C and E

4) Right click and choose Hide

View all Excel hints and tips


Server loaded in 0.1 secs.