code or formula help to move data based on specific cell value.

[steve400243]

Hello, i'm trying to come up with the best way to move data from the Data sheet to the CFS Sheet based on entering the master number in cell C3 on the CFS Sheet ?? Then once moved the sheet would save as that master number on the desktop . any ideas? Please let me know if i need to provide more information. Please see my attached sheet. Thank you for any and all help.
__________________________________________________________________

Mod edit : thread moved to appropriate forum !​

 

[steve400243]

Thank you for the correction.

 

[Nebu]

Hi:

Use the following code

Sub test()
Application.ScreenUpdating = False

Dim rng As Range, copyrng As Range, copyrng1 As Range, copyrange2 As Range
Sheet1.Range("A11:G" & Sheet1.Cells(Rows.Count, 1).End(xlUp).Row).ClearContents

Set rng = Sheet6.Range("A1:E" & Sheet6.Cells(Rows.Count, 1).End(xlUp).Row)
Set copyrng = Sheet6.Range("B2:B" & Sheet6.Cells(Rows.Count, 1).End(xlUp).Row)
Set copyrng1 = Sheet6.Range("C2:C" & Sheet6.Cells(Rows.Count, 1).End(xlUp).Row)
Set copyrng2 = Sheet6.Range("D2:E" & Sheet6.Cells(Rows.Count, 1).End(xlUp).Row)

With rng
.AutoFilter Field:=1, Criteria1:=Sheet1.[C3].Text
copyrng.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheet1.[A11]
copyrng1.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheet1.[C11]
copyrng2.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheet1.[F11]
End With

' Change the path as per your need
ThisWorkbook.SaveAs (ThisWorkbook.Path & "\" & Sheet1.[C3].Text & ".xlsm")

Application.ScreenUpdating = True
End Sub

Note:

You will have to change the saveas path as per your need.

Thanks

 

[steve400243]

Thank you so much Nebu, This works as needed. I appreciate your time.

I have another question if you don't mind? I have some hidden columns ( U thru AH) that i use to calculate in the storage rates, is this the best way to do this? It was all I could come up with with my limited knowledge. Storage is based on the Devanned date from cell H4,

 

[Nebu]

Hi:

There is nothing wrong in using the helper columns, as long as you are good with your logic. I won't be able to give you a ready made formula without knowing the logic you have used to come up with numbers from column U thru AH.

But my guess is that the nof columns you have used can be reduced by combining some of the formulas.

Thanks

 

[steve400243]

the logic for the columns comes from - 7 free calendar days including devan date(cell H5) $3.50 per cbm, or $50.00 min charge for next 5 days, 12th day = additional $3.50 per cbm per day(no min). Thanks again for your help. Its working fine now, but i always like to learn new formulas. If you have time.

 

[Nebu]

Hi:

I have constructed a formula in the attached file. As per my understanding you can combine the cell colored in red to just one column(colored in green). If you can go through the rest of your columns you can eliminate some of the helper columns by combining the columns.

Thanks

 

[steve400243]

Thanks again for your time. I'll play around with it more tomorrow, finally went home for the day.

I Really appreciate it!

 

[steve400243]

Hi:

Use the following code

Sub test()
Application.ScreenUpdating = False

Dim rng As Range, copyrng As Range, copyrng1 As Range, copyrange2 As Range
Sheet1.Range("A11:G" & Sheet1.Cells(Rows.Count, 1).End(xlUp).Row).ClearContents

Set rng = Sheet6.Range("A1:E" & Sheet6.Cells(Rows.Count, 1).End(xlUp).Row)
Set copyrng = Sheet6.Range("B2:B" & Sheet6.Cells(Rows.Count, 1).End(xlUp).Row)
Set copyrng1 = Sheet6.Range("C2:C" & Sheet6.Cells(Rows.Count, 1).End(xlUp).Row)
Set copyrng2 = Sheet6.Range("D2:E" & Sheet6.Cells(Rows.Count, 1).End(xlUp).Row)

With rng
.AutoFilter Field:=1, Criteria1:=Sheet1.[C3].Text
copyrng.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheet1.[A11]
copyrng1.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheet1.[C11]
copyrng2.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheet1.[F11]
End With

' Change the path as per your need
ThisWorkbook.SaveAs (ThisWorkbook.Path & "\" & Sheet1.[C3].Text & ".xlsm")

Application.ScreenUpdating = True
End Sub

Note:

You will have to change the saveas path as per your need.

Thanks

Hello Again Nebu, This code is working great! I Was wondering how it reads the sheets as "Sheet6" and not the sheet name? I thought it had to reference the actual sheet name?

 

[Nebu]

Hi:

You can call sheets in 2 different ways in VBA either by sheet index or by sheet name.

I have used the sheet index method, the advantage of using the sheet index is, even if you rename your tab the macro will work fine. Personally I like index method coz, it is easy to write code using sheet index rather than long sheet names.

If you want to use sheet name method the code will be something like

sheets("Your sheet name")

Thanks

 

[steve400243]

Thank you Nebu, Good information to know! I did not know that.

If I wanted to add more columns of data, Like all columns A thru G, would i just add on to the code in the same format?
Set copyrng2 = Sheet6.Range("D2:E"
Set copyrng3 = Sheet6.Range("E2:F "

 

[Nebu]

Hi:

Yes, since the range you wanted to paste your data was non-contiguous, hence I had to define different ranges , if it is going to be just one continuous range you do not have to declare a range you can use as follows

Sheet6.Range[A11].PasteSpecial

Thanks

 

[steve400243]

Thank you Again Nebu, I have learned alot!