Copy Macro from Bottom to Top

[JoshuaZeno]

Currently, my code has a CTRL-SHIFT-DOWN macro that copies policy information to another spreadsheet. However, the CTRL-SHIFT-DOWN macro does not copy all of the policies as there are sometimes completely blank rows in between filled rows. Could someone please help me find a solution to this where all the rows are pulled (even those AFTER the blank rows)?

 

[mrshl9898]

VBA Code To Find Last Row Or Last Column (Best Way)

Discover the best methods to make your VBA code dynamic by coding in a way that allows your macros to automatically expand and collapse based on the size of each individual data set. This can be done by finding the Last Row or Last Column of your particular data range.

www.thespreadsheetguru.com

Please paste in code, not screen shots of it.

Try adding:

Sub FindingLastRow()

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet

  LastRow = sht.Cells(sht.Rows.Count, "AG").End(xlUp).Row
  lastcolumn = sht.Cells(2, sht.Columns.Count).End(xlToLeft).Column

Range("AG2", Cells(LastRow, lastcolumn)).Copy


End Sub

 

[jolivanes]

If you replace what you show in your picture by the following, does that work? Ideally, you should show the whole code.
Check on a copy of your workbook and see if the resize figures are right.

Dim monthArr
Dim lr As Long, lc As Long, colNr As Long
monthArr = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
lr = Sheets(month).Cells(Rows.Count, 33).End(xlUp).Row
lc = Sheets(month).Cells(2, Columns.Count).End(xlToLeft).Column
colNr = Application.Match(month, monthArr, 0) * 7 - 6
Sheets("IFT").Cells(3, colNr).Resize(lr - 3, lc - 33).Value = Sheets(month),Cells(2, 33).Resize(lr - 2, lc - 33).Value

Minimize "Select" as Much as possible. It is very seldom required.
The use of With ..... End With is usually a good alternative.

FYI, As you have noticed, the xlToRight and xlDown have their limitations.

 

[JoshuaZeno]

This is the whole code, which parts would I modify? Yes, xlToRight and xlDown have their limitations.

Sub ImportData()
  
    Range("D6") = "Running"
    Application.Calculation = xlManual
    Application.ScreenUpdating = False
   
    Dim summary As Workbook
    Set summary = ThisWorkbook
   
    Dim source As Workbook
   
    Dim Location As String
    Location = Worksheets("Macro").Range("D5") + Worksheets("Macro").Range("E5")
   
    Dim sourceName As String
    sourceName = Worksheets("Macro").Range("E5")
   
    Dim month As String
    month = Worksheets("Macro").Range("C5")

'If no such file, message "Input file does not exist."
If Dir(Location) <> "" Then

Sheets(month).Select
    Range("AG:AK").Select
    Selection.ClearContents
    LastRow = ActiveSheet.UsedRange.Rows.Count
    LastCol = ActiveSheet.UsedRange.Columns.Count
    'Range("K3", Cells(LastRow, LastCol).Address(False, False)).Select
    'Selection.ClearContents
    Range("A1").Select

        'Opens file if not already opened
        On Error Resume Next
        Set source = Workbooks(sourceName)
        If Err.Number > 0 Or Err.Number < 0 Then
            Err.Clear
            IsClosed = 1
            Set source = Workbooks.Open(Location)
        End If
       
    source.Worksheets("Data").Activate
    source.Worksheets("Data").Range("AZ:BB, BD:BG").Copy
    'source.Worksheets("Data").Range("BD:BG").Copy
    Application.Goto Reference:="Data"

    summary.Activate
    Sheets(month).Select
    'Range("A2").Select
    'ActiveSheet.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Range("AG1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Range("AG1").Select

    Sheets(month).Select
    Range("AG2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("IFT").Select
    'Range("A3").Select
    If month = "Jan" Then
        Range("A3").Select
    ElseIf month = "Feb" Then
        Range("H3").Select
    ElseIf month = "Mar" Then
        Range("O3").Select
    ElseIf month = "Apr" Then
        Range("V3").Select
    ElseIf month = "May" Then
        Range("AC3").Select
    ElseIf month = "Jun" Then
        Range("AJ3").Select
    ElseIf month = "Jul" Then
        Range("AQ3").Select
    ElseIf month = "Aug" Then
        Range("AX3").Select
    ElseIf month = "Sep" Then
        Range("BE3").Select
    ElseIf month = "Oct" Then
        Range("BK3").Select
    ElseIf month = "Nov" Then
        Range("BS3").Select
    ElseIf month = "Dec" Then
        Range("BZ3").Select
    End If
   
    ActiveSheet.Paste
    Sheets(month).Select
    Range("AG1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents

    Sheets(month).Select
    Range("AA2:AF2").Select
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Selection.AutoFill Destination:=Range("AA2:AF" & LastRow)
    Range("AA3:AF" & LastRow).Select
    Selection.Font.ColorIndex = 0
    Selection.Font.Bold = False
    Selection.Interior.Pattern = xlNone
    Selection.Interior.TintAndShade = 0
    Selection.Interior.PatternTintAndShade = 0
    Range("A1").Select
   
    'Closes if opened
    If IsClosed = 1 Then
        source.Close savechanges:=False
    End If
    IsClosed = 0
   
'Else
'MsgBox "Input file does not exist"
'End
End If
   
    Sheets("Macro").Select
    Range("A1").Select
  
Range("D6") = ""
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
   
End Sub

 

[jolivanes]

Organize your code to something similar like this.

Dim summary As Workbook
Dim source As Workbook
Dim Location As String
Dim sourceName As String
Dim month As String
Dim wsM As Worksheet
Dim monthArr
monthArr = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

Set wsM = Worksheets("Macro")
Location = wsM.Range("D5") & wsM.Range("E5")
sourceName = wsM.Range("E5")
month = wsM.Range("C5")
LastRow = Sheets(month).Cells.Find("*", , , , xlByRows, xlPrevious).Row
LastCol = Sheets(month).Cells.Find("*", , , , xlByColumns, xlPrevious).Column

Set summary = ThisWorkbook
Set source = Workbooks(sourceName)

I would replace the following

LastRow = ActiveSheet.UsedRange.Rows.Count
LastCol = ActiveSheet.UsedRange.Columns.Count

by what I have above.
UsedRange can give you wrong results. Imagine your data starting at Range("E5") and ends at Range("M20").
You want 20 for LastRow and 13 for LastCol but you'll get 16 and 9

It looks like these 33 lines

Sheets(month).Select
    Range("AG2").Select
    Range(Selection, Selection.End(xlToRight)).Select

    more lines here

    ElseIf month = "Dec" Then
        Range("BZ3").Select
    End If
    ActiveSheet.Paste

could be replaced by these 2 lines.

colNr = Application.Match(month, monthArr, 0) * 7 - 6
Sheets("IFT").Cells(3, colNr).Resize(lr - 3, lc - 33).Value = Sheets(month),Cells(2, 33).Resize(lr - 2, lc - 33).Value

All you can do is try on a copy of your workbook.

Read up on "Selecting in excel macros" (Google and this Forum are your best friends)