Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-21-2018, 09:27 PM
RosieSummers RosieSummers is offline unable to retrieve data Windows 7 64bit unable to retrieve data Office 2016
Novice
unable to retrieve data
 
Join Date: Feb 2018
Posts: 9
RosieSummers is on a distinguished road
Default unable to retrieve data

Hi there,



i have a VBA code that retrieves its data from an external excel workbook(masterplandata workbook) and copies it onto the (example workbook).

what the VBA code does:
1. retrieve the data for July 2018 by the fabric delivery column
2. copy the data according to columns of the example workbook
3. vba would not clear records in the example workbook with OFM, KH and Collar and Cuff as these are typed in by the users.
4. sort the records according to the PO column

Now with the code, i am not able to copy over the records. does anyone know why?

i have attached the example workbook below
Attached Files
File Type: xlsx MasterPlanData.xlsx (12.5 KB, 20 views)
File Type: xlsm Example.xlsm (24.3 KB, 10 views)
Reply With Quote
  #2  
Old 02-22-2018, 10:35 AM
NoSparks NoSparks is offline unable to retrieve data Windows 7 64bit unable to retrieve data Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Quote:
Now with the code, i am not able to copy over the records. does anyone know why?
Now would indicate that you could before. Could you?
The why is because the b array is not being written to the sheet.

Care to post a link to where you got the code ?
Reply With Quote
  #3  
Old 02-22-2018, 05:58 PM
RosieSummers RosieSummers is offline unable to retrieve data Windows 7 64bit unable to retrieve data Office 2016
Novice
unable to retrieve data
 
Join Date: Feb 2018
Posts: 9
RosieSummers is on a distinguished road
Default

Code:
Sub July()
Dim arr, c, b(), n&
Application.ScreenUpdating = False
Worksheets("July 2018").Range("A4").AutoFilter
Workbooks.Open "C:\Users\sophia.tan\Desktop\MasterPlanData.xlsx", 0, 1
arr = Sheets("Excel").UsedRange
ActiveWorkbook.Close 0
c = Array(0, 2, 13, 14, 7, 8, 11, 1, 9, 10, 16, 17, 20, 22, 15, 30, 27, 28, 29, 3, 4, 39)
d = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 14, 15, 16, 17, 18, 19, 20, 21, 23)
ReDim b(1 To UBound(arr), 1 To 23)

For i = 2 To UBound(arr)
    If arr(i, 13) >= DateSerial(Year:=2018, Month:=7, Day:=1) And arr(i, 12) <= DateSerial(Year:=2018, Month:=7, Day:=31) Then
        n = n + 1
        For j = 1 To UBound(c)
            b(n, d(j)) = arr(i, c(j))
        Next
    End If
Next

With Worksheets("Sheet2")
    .Range("A4:W" & Rows.Count).CurrentRegion.SpecialCells(xlCellTypeVisible).AutoFilter field:=1, Criteria1:="<>OFM"
    .Range("A4:W" & Rows.Count).CurrentRegion.SpecialCells(xlCellTypeVisible).AutoFilter field:=13, Criteria1:="<>Collar & Cuff"
    .Range("A4:W" & Rows.Count).CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).Clear
    .Range("A4:W" & Rows.Count).Resize(UBound(b, 1), UBound(b, 2)) = b
    .AutoFilter.ShowAllData
    .Range("A4").CurrentRegion.Sort key1:=Range("G6"), order1:=xlAscending, Header:=xlYes
    .Range("A4").Select
    End With
Call Fabrication
Application.ScreenUpdating = 1
End Sub
this was my previous code. i wanted the vba code to only clear the range of A4:W however this code would clear the entire row. hence i there was a change.
Reply With Quote
  #4  
Old 02-22-2018, 10:32 PM
NoSparks NoSparks is offline unable to retrieve data Windows 7 64bit unable to retrieve data Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Afraid I don't really follow what you're trying to do,
but this is the line that wrote the b array to the sheet
Code:
.Range("A4:W" & Rows.Count).Resize(UBound(b, 1), UBound(b, 2)) = b
Reply With Quote
  #5  
Old 02-23-2018, 12:43 AM
RosieSummers RosieSummers is offline unable to retrieve data Windows 7 64bit unable to retrieve data Office 2016
Novice
unable to retrieve data
 
Join Date: Feb 2018
Posts: 9
RosieSummers is on a distinguished road
Default

if i use the above code
Code:
.Range("A4:W" & Rows.Count).CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).Clear
when i type the OFM in the records and run the code, it would produce additional records which are not suppose to be there.

i have attached the excelworksheet below. the error are in red
Attached Files
File Type: xlsm error.xlsm (23.0 KB, 7 views)
Reply With Quote
  #6  
Old 02-23-2018, 02:40 PM
NoSparks NoSparks is offline unable to retrieve data Windows 7 64bit unable to retrieve data Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Not all of the red is error, unless you're not filtering the way you want.

See if this does what you're after.

Because your sample columns A:W differ in length, lastRow is calculated different than originally.
The loop that populates "M" based on "J" I've only applied to newly added data.
Only columns A:W portion of rows are deleted, column "Y" cells are numbers to verify this.
There is a button to put the original sample values back on the sheet so you can easily play with it over and over.
If you uncomment the STOP instruction in the code and split your screen half code and half sheet,
you can step through the code a line at a time with the F8 key and watch what happens on the sheet with execution of each instruction.
I hope I've commented the code enough to follow without too much trouble.
Attached Files
File Type: xlsm error_altered.xlsm (32.7 KB, 7 views)
Reply With Quote
  #7  
Old 02-27-2018, 12:13 AM
RosieSummers RosieSummers is offline unable to retrieve data Windows 7 64bit unable to retrieve data Office 2016
Novice
unable to retrieve data
 
Join Date: Feb 2018
Posts: 9
RosieSummers is on a distinguished road
Default

Thanks this is what i am looking for.

however the code still cannot auto filter for Collar & Cuff and whenever i click on the button it would paste the same row again.

it there a way for it to not have duplicate?

i have attahced the duplicated excel workbook
Attached Files
File Type: xlsm error_altered2.xlsm (30.3 KB, 9 views)
Reply With Quote
  #8  
Old 02-27-2018, 01:11 AM
RosieSummers RosieSummers is offline unable to retrieve data Windows 7 64bit unable to retrieve data Office 2016
Novice
unable to retrieve data
 
Join Date: Feb 2018
Posts: 9
RosieSummers is on a distinguished road
Default

i ran through the codes with the F8 key and the autofilter seems to filter all the records instead of the only the stated criteria
Reply With Quote
  #9  
Old 02-27-2018, 08:34 AM
NoSparks NoSparks is offline unable to retrieve data Windows 7 64bit unable to retrieve data Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

No cells in column A contain OFM and KH.
I suspect the filter operator should be xlOR,
and you've changed things from the original file displaying rows where A is not OFM to displaying rows where A is OFM,
and neither line displayed after filtering for OFM or KH has Collar & Cuff in column M.

So, I'm not sure what it is you want the filters to do, can you put into words, what should be displayed on screen after the filters have been applied.

Quote:
and whenever i click on the button it would paste the same row again.
it there a way for it to not have duplicate?
Excel has a built in Remove Duplicates function on the Data Ribbon in the Data Tools section.

Also, please alter the clear what's left section of code to be
Code:
        ' clear what's left
        On Error Resume Next   'suppress error notification in case nothing visible
        .Offset(1).SpecialCells(xlCellTypeVisible).ClearContents
        On Error GoTo 0         'remove suppression of error notification
Reply With Quote
  #10  
Old 03-04-2018, 04:14 AM
RosieSummers RosieSummers is offline unable to retrieve data Windows 7 64bit unable to retrieve data Office 2016
Novice
unable to retrieve data
 
Join Date: Feb 2018
Posts: 9
RosieSummers is on a distinguished road
Default

sorry for the confusion, i would like to the code to filter out rows that contain OFM and KH in column A and also filter out rows with Collar & Cuff in column M.
so that the excel would not delete these rows.

for the duplicate part, what i meant was for example in the external workbook, in this case, there is 5 rows to be copied over. Hence no matter how many times i click on the button, the excel worksheet would only show the 5 rows. however in this code now, if i click the button again, it would copy over the same 5 rows below the previous one.

i have attached a excel worksheet example of what happens if i click on the button three times. it shows that the same 5 rows three times. those in red are suppose to appear only once
Attached Files
File Type: xlsm error_altered2.xlsm (30.8 KB, 9 views)
Reply With Quote
  #11  
Old 03-04-2018, 10:44 AM
NoSparks NoSparks is offline unable to retrieve data Windows 7 64bit unable to retrieve data Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Sorry RosieSummers, currently I am no longer participating on this forum.
Reply With Quote
  #12  
Old 03-05-2018, 01:10 AM
RosieSummers RosieSummers is offline unable to retrieve data Windows 7 64bit unable to retrieve data Office 2016
Novice
unable to retrieve data
 
Join Date: Feb 2018
Posts: 9
RosieSummers is on a distinguished road
Default

is there any other forums that i can contact u on to get help on this?
Reply With Quote
  #13  
Old 03-08-2018, 10:59 AM
NoSparks NoSparks is offline unable to retrieve data Windows 7 64bit unable to retrieve data Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Are still needing assistance with this ?
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
unable to retrieve data "word was unable to open the data source" error meganadand Mail Merge 6 02-27-2020 11:25 AM
unable to retrieve data Retrieve the last activated document name. eduzs Word VBA 1 08-22-2017 03:11 PM
Unable to open Outlook, receiving message unable to open because (myaccount)@gmail.c Flyin' Aces Tattoo Outlook 0 04-20-2013 11:56 AM
Retrieve PPS files uncledewey PowerPoint 2 08-10-2012 09:08 AM
Microsoft Scheduler retrieve Calendar data badda Office 0 11-22-2009 06:04 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:45 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft