Internet Codes from 1 Excel sheet to another

[Risque666]

Hi All
I have a complex spreadsheet I am trying to work on. But I am unsure where to start.
Please see the 2 sheets enclosed. The once called Internet Codes is all the Data I get from a program. I need to make a Macro that takes away all the crap from the sheet but leaves me the lines with only the internet codes.
Example: Below I have highlighted 2 codes out of the 400 codes the sheet has, I only want the codes and all the other crap removed.

Once the Macro has removed all the other rubbish - I should be left with just 400 cells with the 10 Digit codes on each cell.
Then if you look at the WIFI Code sheet - I want to know is there a way that I can make a macro that will allow me to import the codes into the cells on only 9 of the places at a time.
Example Below I have highlighted where the codes need to go on the 9 WiFi code inserts.

Is there away to do it so I put the macro and it inserts a new 9 codes after i print each one.

If you need more info let me know.. Im completely lost where to start on this.
let me know if there is a solution to make this work.. at least the first part at best to clear all rubbish but the codes.

 

[Risque666]

Depending on you actual data this should put the codes into column B without blanks

Sub Risque()
   With Range("A1", Range("A" & Rows.Count).End(xlUp))
      .Offset(, 1).Value = Evaluate("if(right(" & .Address & ",4)=""days""," & .Offset(1).Address & ","""")")
      .Offset(, 1).SpecialCells(xlBlanks).Delete xlShiftUp
   End With
End Sub

Ok Guys I have now go it down to the point where all trash info is deleted and all codes are in Column B
Now I have this Macro to put the Codes on Sheet 1 (called ""Inserts"") in all the right places.. Here is the coding for the first 4 codes in the correct place.
Do you have a code to improve this

Range("B6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Inserts").Select
Range("C10:F11").Select
ActiveSheet.Paste
Sheets("Codes").Select
Range("B7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Inserts").Select
Range("K10:N11").Select
ActiveSheet.Paste
Sheets("Codes").Select
Range("B8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Inserts").Select
Range("S10:V11").Select
ActiveSheet.Paste
Sheets("Codes").Select
Range("B9").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Inserts").Select
Range("C23:F24").Select
ActiveSheet.Paste

 

[maabadi]

Try one of these two codes:
Code 1:

Sub test()
Sheets("Codes").Range("B6").Copy Sheets("Inserts").Range("C10:F11")
Sheets("Codes").Range("B7").Copy Sheets("Inserts").Range("K10:N11")
Sheets("Codes").Range("B8").Copy Sheets("Inserts").Range("S10:V11")
Sheets("Codes").Range("B9").Copy Sheets("Inserts").Range("C23:F24")
End Sub

Code 2:

Sub test2()
Dim i As Long, Sh1 As Worksheet, Sh2 As Worksheet
Set Sh1 = Sheets("Codes")
Set Sh2 = Sheets("Insert")
For i = 1 To 3
Sh1.Range("B" & i + 5).Copy Range(Sh2.Cells(3 + (i - 1) * 8, 10), Sh2.Cells(6 + (i - 1) * 8, 11))
Next i
Sh1.Range("B9").Copy Sh2.Range("C23:F24")
End Sub

 

[maabadi]

1. First I change Codes to work Betters.
2. At Inserts Worksheet Some rows after Page 12 Causes to Macro don't Work Correct, Then I delete that rows and Sent your file for you.
3. your template only have 198 spaces for wifi codes NOT More. (22 * 9 = 198). if you want to add template to more rows. please Note to:
a. each pages have exact 40 rows.
b. wifi Codes that pasted from Inserts sheet should be Placed at row 10, 23 & 37 at each Page.
4. At Module 1 , I add your first code (FLuff Code) to Delete not wanted data & then Sub Test1 OR Test2 Can used to insert codes to other sheet
5. Test1 codes, Places code to each sheet then print at address you see front of file name, if you want you can change path to what you want:
F:/PrintPDFs Can be changed to your Path.
6. Test2 Codes Places all codes at Codes sheet & then Print all at one file. at this one also you can change file Path.
7. At Module 2, I combine Fluff Code with My codes at two separate Macro
8. I upload your ready file with macro for you.
YourExcelModified

Sub Risque()
   With Range("A1", Range("A" & Rows.Count).End(xlUp))
      .Offset(, 1).Value = Evaluate("if(right(" & .Address & ",4)=""days""," & .Offset(1).Address & ","""")")
      .Offset(, 1).SpecialCells(xlBlanks).Delete xlShiftUp
   End With
    Columns(1).Delete
End Sub

Sub test1()
Dim i As Long, Sh1 As Worksheet, Sh2 As Worksheet, Lr As Long, K As Long
Set Sh1 = Sheets("Codes")
Set Sh2 = Sheets("Inserts")
Lr = Sh1.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To Lr Step 9
Sh2.Range("C10").Value = Sh1.Range("A" & i).Value
Sh2.Range("K10").Value = Sh1.Range("A" & i + 1).Value
Sh2.Range("S10").Value = Sh1.Range("A" & i + 2).Value
Sh2.Range("C23").Value = Sh1.Range("A" & i + 3).Value
Sh2.Range("K23").Value = Sh1.Range("A" & i + 4).Value
Sh2.Range("S23").Value = Sh1.Range("A" & i + 5).Value
Sh2.Range("C37").Value = Sh1.Range("A" & i + 6).Value
Sh2.Range("K37").Value = Sh1.Range("A" & i + 7).Value
Sh2.Range("S37").Value = Sh1.Range("A" & i + 8).Value
K = K + 1
Sh2.ExportAsFixedFormat Type:=xlTypePDF, Filename:="F:/PrintPDFs/Print" & K & ".pdf", Quality:=xlQualityStandard, _
From:=1, To:=1, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False
Next i
End Sub

Sub test2()
Dim i As Long, Sh1 As Worksheet, Sh2 As Worksheet, Lr As Long, M As Long, N As Long, j As Long, K As Long
Set Sh1 = Sheets("Codes")
Set Sh2 = Sheets("Inserts")
Lr = Sh1.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To Lr
M = (i - 1) Mod 3
j = M * 8 + 3
N = Int((i - 1) / 3) * 13 + 10
K = Int((i + 2) / 9)
N = N + K
Sh2.Cells(N, j).Value = Sh1.Range("A" & i).Value
Next i
Sh2.ExportAsFixedFormat Type:=xlTypePDF, Filename:="F:/PrintPDFs/Print" & K & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False
For i = 1 To Lr1
M = (i - 1) Mod 3
j = M * 8 + 3
N = Int((i - 1) / 3) * 13 + 10
K = Int((i + 2) / 9)
N = N + K
Sh2.Cells(N, j).ClearContents
Next i
Sh1.Range("A1:A" & Lr).ClearContents
End Sub

 

[Risque666]

Ok so did you put the excel sheet back in google box? and did you rename it..
Does it have the macros in it?

 

[maabadi]

Try this file I separate 3 Macro for your works.
YourModifiedExcelfile