Copying data from one spreadsheet to another

[shadows200354]

Hi,
I did look for someone to help me copy data from one spreadsheet to another. There is a slight catch, the data original source changes name and file path every month however the destination doesn't (if that makes things easier).

What i need you guys expertise is to copy data from original source and paste values and and format into the new static spreadsheet, from all the tabs in the orignal docs about 12 to 13 tabs (if its easier I will button activate them). the cells the data is picked up from needs to correspond to the ones where the data is dumped effectively as I have another macro that need to be run once the data is moved. (in the static document the macro just reformat the data) in case you need to see the macro I will paste the code below.


This was a simple code that i used the record function to get the vba and adapt it to use with the button click function. (i am pretty novice when it comes to vba coding)

Sub Button2_Click()'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+m
'
    Columns("H:H").Select
    Selection.Copy
    Columns("I:I").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("I:I").EntireColumn.AutoFit
    Columns("U:U").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("H:H").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("U6:Z28").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Columns("U:X").Select
    Selection.ClearContents
    Selection.Delete Shift:=xlToLeft
    Columns("C:C").Select
    Selection.Copy
    Columns("K:K").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Columns("D:D").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("J:J").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("I:I").EntireColumn.AutoFit
    Columns("B:F").Select
    Application.CutCopyMode = False
    Selection.ClearContents


End Sub

 

[shadows200354]

Hi All, thank you for looking at this problem. Just an FYI I have found a solution to my problem, I thought laterally and re-designed the spreadsheet. used this code crash:code not written by me) but if someone wants the solve the issue still i would be forever grateful if not then I am grateful for them to having read the issue in the first place. code I used was,

'Assuming no current buttons existDim sh1 As Worksheet, sh2 As Worksheet, lr As Long
Set sh1 = Sheets("formatting sheet")
Set sh2 = Sheets("Social Media")
lr = sh2.Cells(Rows.Count, 1).End(xlUp).Row
sh2.Range("A4:V" & lr).Copy sh1.Cells(Rows.Count, 1).End(xlUp)(2)
sh1.Cells(Rows.Count, "X").End(xlUp).Resize(lr - 2, 3).FillDown

I will tweak it so it more suited by thats the general code.

thank you all