Copying Data over from one workbook to another issue

[dreen]

I have two workbooks for my code, one is a database (The "Changes" sheet in my example) and the other is an input sheet "(Codename "Sheet1" as my code is being written inside this workbook where Sheet1 is). I am trying to have the code check if the value of certain cells in column "K' of Sheet 1 are equal to something (<>""), and if they are, I am trying to filter the "Changes" sheet to filter out which row in column A has the value of cell "H4" from Sheet1, so that I can copy and paste the value of "K30" from Sheet1 (from the workbook where the code is being written) into cell "F6" [cells (1,6)] of the other workbook (the "Changes" sheet)

'Module 8: Sends the requested changes over to the "Changes" sheet

Sub SendChanges()

Set Cd = Workbooks.Open("\\FILEPATH\Changes_Database_IRR_20-2S_New.xlsm")
Set Changes = Cd.Sheets("Changes")

Changes.Activate
ActiveSheet.Unprotect "Swrf"
Sheet1.Unprotect "Swrf"

'Only executes this macro if the the new/change requested value in column "K" of the Operator sheet (Codename Sheet1) has a numerical value present
If Sheet1.Range("K30").Value <> "" Then

'Filters the Changes_Database for the part name & process (the key) which is in cell "H4" of the Operator sheet (Codename Sheet1)
Changes.Range("A1").AutoFilter Field:=1, Criteria1:=Sheet1.Range("H4")
 
'Copies the changed content in cell "K30" from the Operator Sheet (Codename Sheet1)
Sheet1.Range("K30").Copy

'Finds the row in the Changes_Database ("Changes" sheet) that has matched all filters and;
'Pastes the value of cell "K30" (From Codename "Sheet1" or Operator sheet) into the matching parameter cell in the Changes_Database,which is in column 6 in this case
Changes.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 6).PasteSpecial xlPasteValues

'Removes all filters and shows all data'
Changes.ShowAllData

End If

ActiveSheet.Protect "Swrf"
            ActiveWorkbook.Save
            ActiveWorkbook.Close SaveChanges:=True
Sheet1.Protect "Swrf"

End Sub

 

[Fluff]

Cells(1,6) is F1 not F6
Are you trying to put the value from K30 in F6? In which case what's the point of the autofilter?
Or are you trying to put the value of K30 into col F of the first visible row?

 

[dreen]

Cells(1,6) is F1 not F6
Are you trying to put the value from K30 in F6? In which case what's the point of the autofilter?
Or are you trying to put the value of K30 into col F of the first visible row?

From my understanding, cells(1,6) is Row 1, Column 6. and yes exactly, I am trying to put the value of K30 into col F of the first visible row

 

[Fluff]

From my understanding, cells(1,6) is Row 1, Column 6

This is totally correct, which means F1, not F6 as you said (F6 is row 6, column 6)

That said, in what way is your code not working?

 

[dreen]

I have heading in row 1, for example A1 has "Key" in it, which is where I am filtering "H4" from. Would that mean that I have to paste K30 it into F2 (row 2, column 6) as it will become visible underneath my headers? and my code is not pasting any values, both workbooks are just remaining opened (this is a module).

 

[Fluff]

You're code should put the value from K30 into the first visible row after the filter has been applied.

Step through your code using F8 until you get to this line

Changes.ShowAllData

then look at the sheet, do you have any visible rows, below the header?
If yes, does that first visible row contain the value of K30?

 

[dreen]

I just made two test workbooks, and I put in same code into test 1, and here is what is happening.

Private Sub CommandButton1_Click()
'Module 8: Sends the requested changes over to the "Mytest2" sheet

Set Cd = Workbooks.Open("\\schaeffler.com\stratford\DATA\NEA-FBA-P\Projects\SetupSheets\Databases\Test 2.xlsm")
Set Changes = Cd.Sheets("Mytest2")

Changes.Activate
ActiveSheet.Unprotect "Swarf"
Sheet1.Unprotect "Swarf"

'Only executes this macro if the the new/change requested value in column "K" of the Operator sheet (Codename "Sheet1") has a numerical value present
If Sheet1.Range("K2").Value <> "" Then

'Filters the Changes_Database for the part name & process (the key) which is in cell "H4" of the Operator sheet
Changes.Range("A1").AutoFilter Field:=1, Criteria1:=Sheet1.Range("H4")
 
'Copies the changed content in cell "K30" from the Operator Sheet
Sheet1.Range("K2").Copy

'Finds the row in the Changes_Database that has matched all filters and;
'Pastes the value of cell "K30" into the matching parameter cell in the Changes_Database,which is in column 6 in this case
Changes.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 6).PasteSpecial xlPasteValues

'Removes all filters and shows all data'
Changes.ShowAllData


Changes.Protect "Swarf"
            ActiveWorkbook.Save
            ActiveWorkbook.Close SaveChanges:=True

End If

End Sub

 

[Fluff]

Can you please answer my questions?