98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Macro in Excel
Macro in Excel
Resolved · 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 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
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Hide separate columns in Excel 2010If you want to hide columns not adjacent to each other for example, Columns A, C and E then:- |